Solved

Sum based on custom date ranges

Posted on 2016-10-07
4
57 Views
Last Modified: 2016-10-11
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
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 41834754
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
 

Author Closing Comment

by:pdvsa
ID: 41834766
brilliant. simply brilliant.  I can definitely use that.  thank you very much.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41834939
You're very welcome.  Thanks for closing the question so promptly.

Good luck with the rest of your project.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41838277
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question