Column values ​​based on date

Question

I wrote this function, which will give me a monthly amount for two columns: each has a date for each order, each has a cost for 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) 

Use of such data:

 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:

 May 998 June 968.28 July 1239.76 August 514.96 

However, now I would like to make daily amounts and using my path, I need to manually edit each line.

How can I do it better in Excel?

Answer 1

Use a column to display each date as a month number; another column for day number:

  ABCD ----- ----- ----------- -------- 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 monthly amounts, put month month next to each month:

  EFG ----- ----- ------------- 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.

Just a day will be a little harder, but you'll probably see how to do it.

Answer 2

Use pivot tables, this will definitely save you time. If you are using excel 2007+, use tables (structured links) to save the dynamic table. However, if you insist on using features, go with the Smandoli suggestion. Again, if you are using 2007+, use SUMIFS, which is faster compared to SUMIF.

Answer 3

In response to Nikita's answer, there is a good explanation of pivot tables: http://peltiertech.com/WordPress/grouping-by-date-in-a-pivot-table/

In Excel 2007, you will create a pivot table, make the "Date" column a row label, and a value in the "Amount" column. Then right-click on one of the row labels (for example, date), right-click and select "Group". Then you will be able to group by day, month, etc.

Personally, how would I go.

If you prefer formulas, Smandoli's answer will give you most of the way. To use Sumif per day, you must add a column with the formula, for example:

 =DATE(YEAR(C1), MONTH(C1), DAY(C1)) 

where column C contains your dates.

Then you can use this in your vault.

Answer 4

Add a column to your existing data to get rid of the hour: minute: second timestamp on each row:

  =DATE(YEAR(A1), MONTH(A1), DAY(A1)) 

Increase data length. Even simpler: stop hh: mm: ss data collection if you don't need it. Assuming your date / time was in column A, and your value was in column B, you would put the above formula in column C and automatically expand it for all your data.

Now, in another column (say, E), create a series of dates corresponding to each day of the specific month in which you are interested. Just enter the first date (e.g. 10/7/2016 in E1), and automatically renew. Then in the cell next to the first date F1 enter:

 =SUMIF(C:C, E1, B:B ) 

automatically expand the formula to cover each date of the month, and you're done. Start on January 1, 2012 and automatically renew for the whole year if you want.

Answer 5

If the second line has the same template as the first line, you just need to edit the first line manually, then you place the mouse pointer in the lower right corner, at the same time, press ctrl to drag the cell down, the template should be copied automatically .