Sum based on custom date ranges

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
pdvsaProject financeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
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
0
 
pdvsaProject financeAuthor Commented:
brilliant. simply brilliant.  I can definitely use that.  thank you very much.
0
 
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.  Thanks for closing the question so promptly.

Good luck with the rest of your project.
0
 
Rob HensonFinance AnalystCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.