Sum based on custom date ranges

pdvsa
pdvsa used Ask the Experts™
on
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
EE-date-intervals.xlsx
Comment
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]:
=SUMPRODUCT((DAY($A4:$A47)>=$R4)*(DAY($A4:$A47)<=$S4)*(MONTH($A4:$A47)=$T4)*(YEAR($A4:$A47)=$U4)*($E4:$E47))

Formula in cell [W4]:
=SUMPRODUCT((DAY($A4:$A47)>=$R4)*(DAY($A4:$A47)<=$S4)*(MONTH($A4:$A47)=$T4)*(YEAR($A4:$A47)=$U4)*($G4:$G47))

Please see the attached workbook.
Q_28975177.xlsx
pdvsaProject finance

Author

Commented:
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

Commented:
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