Calculating amounts based on Due date

[msw]

I have an Excel spreadsheet with two columns B & C. Column B is the Due
Date in Date format (Thursday, April 1, 2004). Column C is an Amount in
Currency format.

For Column C, I would like to have six totals as follows:

Total for Due Dates between 1st and the 5th:
Total for Due Dates between 6th and the 10th:
Total for Due Dates between 11th and the 15th:
Total for Due Dates between 16th and the 20th:
Total for Due Dates between 21st and the 25th:
Total for Due Dates between 26st and the 30th:

When I add the amount for each one of this total, the formula will group the
amount based upon the due date range.

Is there a way that I can achieve this in Excel?

Any help is greatly appreciated.

Thank you in advance.

 

[Dave Hawley]

Hi msw

I would make use of Data>Subtotals. Sort you columns by "Due Date" In
another column, say Column A enter the number 1 in the first 5 cells
(A2:A6), now select cells A2:A11, now drag down with the Fill Handle
(small black square bottom right of selection) as many rows as needed.

Now apply Data>Subtotals using At each change in Column A.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[msw]

I already try that but I have a long list of Suppliers and it get updated
every day.

 

[DDM]

msw, here is the formula (1 of 6, of course) you need:

=SUMPRODUCT((B2:B62>=DATE(2004,1,1))*(B2:B62<=DATE(2004,1,5)),C2:C62)

Adjust the cell references and dates as necessary. (DDM thanks Frank Kabel
for the formula. See his post of 3/27/04).

DDM
"DDM's Microsoft Office Tips and Tricks"
http://ddmara.tripod.com

 

[Ken Wright]

What about the 31st or does no-one pay then??
Is this all one month or how do your groupings distinguish between other months?

You could simply add a column that uses a formula to strip out the day value for
each record, then dump it all into pivot table and group that day value based on
the groupings you provided. Using a dynamic range would then save having to
update for new records being added.