• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

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
0
pdvsa
Asked:
pdvsa
  • 2
1 Solution
 
[ fanpages ]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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now