Sum based on custom date ranges

pdvsa used Ask the Experts™

I am wishing to sum a data set based on the following date intervals:
"1-5"   "6-15"   "16-25"    "26- end of month"

Grateful for your help.
Attached a sample file
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Is this any use to you?

Q_28975177 Sample Results using SUMPRODUCT(...)
Formula in cell [V4]:

Formula in cell [W4]:

Please see the attached workbook.
pdvsaProject finance


brilliant. simply brilliant.  I can definitely use that.  thank you very much.
You're very welcome.  Thanks for closing the question so promptly.

Good luck with the rest of your project.
Rob HensonFinance Analyst

With a minor tweak to the data you could also use a Pivot Table.

In a spare area of the sheet set up the following small table:

1      1-5
6      6-15
16      16-25
26      26-Month End

In a new column next to your data use the following formula to group the dates:

=VLOOKUP(DAY(A4),$T$3:$U$6,2)   where T3:U6 is your small table.

You can then use the Group field as a row value in a Pivot Table to summarise the data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial