Sum values in a column based on date

Question

I have written this function that will give me a monthly sum for two columns  one has the date of each order  one has the cost of each order    SUMIF  C 1  C 1000   gt    amp DATE 2010 6 1   D 1  D 1000 -SUMIF  C 1  C 1000   gt    amp DATE 2010 7 1   D 1  D 1000    Using data like this   8 16 10 17 00 7 99 8 16 10 14 25 7 99  8 15 10 22 42 7 99   I end up with a table like this   May     998 June    968 28 July   1239 76 August  514 96   However  now I would like to do daily sums and using my way I have to hand edit each row     How can I do this better in Excel

User · Accepted Answer

Use a column to let each date be shown as month number; another column for day number:

      A      B       C         D
   -----  ----- ----------- --------
1     8      6    8/6/2010   12.70
2     8      7    8/7/2010   10.50
3     8      7    8/7/2010    7.10
4     8      9    8/9/2010   10.50
5     8     10   8/10/2010   15.00

The formula for A1 is =Month(C1)

The formula for B1 is =Day(C1)

For Month sums, put the month number next to each month:

      E      F         G     
   -----  ----- -------------  
1     7    July   $1,000,010 
2     8     Aug   $1,200,300

The formula for G1 is =SumIf($A$1:$A$100, E1, $D$1:$D$100). This is a portable formula; just copy it down.

Total for the day will be be a bit more complicated, but you can probably see how to do it.

User · Answer

Following up on Niketya s answer  there s a good explanation of Pivot Tables here  http   peltiertech com WordPress grouping-by-date-in-a-pivot-table   For Excel 2007 you d create the Pivot Table  make your Date column a Row Label  your Amount column a value   You d then right click on one of the row labels  ie a date   right click and select Group   You d then get the option to group by day  month  etc   Personally that s the way I d go   If you prefer formulae  Smandoli s answer would get you most of the way there   To be able to use Sumif by day  you d add a column with a formula like    DATE YEAR C1   MONTH C1   DAY C1     where column C contains your datetimes   You can then use this in your sumif

User · Answer

Use pivot tables  it will definitely save you time   If you are using excel 2007  use tables  structured references  to keep your table dynamic   However if you insist on using functions  go with Smandoli s suggestion   Again  if you are on 2007  use SUMIFS  it s faster compared to SUMIF

User · Answer

If the second row has the same pattern as the first row  you just need edit first row manually  then you position your mouse pointer to the bottom-right corner  in the mean time  press ctrl key to drag the cell down  the pattern should be copied automatically

User · Answer

Add a column to your existing data to get rid of the hour minute second time stamp on each row      DATE YEAR A1   MONTH A1   DAY A1     Extend this down the length of your data   Even easier  quit collecting the hh mm ss data if you don t need it   Assuming your date time was in column A  and your value was in column B  you d put the above formula in column C  and auto-extend it for all your data   Now  in another column  let s say E   create a series of dates corresponding to each day of the specific month you re interested in   Just type the first date   for example  10 7 2016 in E1   and auto-extend   Then  in the cell next to the first date  F1  enter    SUMIF C C  E1  B B     autoextend the formula to cover every date in the month  and you re done   Begin at 1 1 2016  and auto-extend for the whole year if you like

[excel] Sum values in a column based on date

Examples related to excel