Sum values in a column based on date

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,">="&DATE(2010,6,1),$D$1:$D$1000)-SUMIF($C$1:$C$1000,">="&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 开发者_如何学Gothis better in Excel?

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.

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.

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.

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.

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.