Sum by date ranges...

[shhhhh22]

Hello All,

I have a sheet that has about 1200 rows. Each row has multiple columns. Date (MMDDYY), Total value, description.

On another page I want to figure out how to add the total values if the dates fall into certain ranges.

-Three Months or less
-Over three months through 12 months
-Over one year through 3 years
etc

it needs to reference the report date say cell A1. and calculate the ranges that way.

so if the dates are Over one year through 3 years from today add up the totals.

is what I'm asking even possible?


Thank you all very much.

[daddylonglegs]

Try using SUMIFS, something like this:

=SUMIFS(Value_range,Date_range,">="&EDATE(A1,12),Date_range,"<"&EDATE(A1,3))

So that uses a date in A1 as the reference (today's date?) and then sums for all dates between 3 and 12 months ago, adapt to suit

[Richard Buttrey]

The approach I'd use is a Pivot Table

Add an additional helper column which works out which age band the record belongs in. e.g.

Please Login or Register  to view this content.

That will cater for the 3 groups you indicate. Just add more IF statements if there are only two or three etcs.

Otherwise create a lookup table and with 0,3,12,36...in the first column and <3, 3-12 months, over 12 months...ect in the 2nd columns and use the following formula

Please Login or Register  to view this content.

[shhhhh22]

Thank you both very much, I will mess around with both solutions and find what works...

[shhhhh22]

Thank you for your suggestions, I am clearly doing something wrong...

this is the formula I am using:
SUMIFS('Report'!$H$2:$H$1200,'Report'!$L$2:$L$1200,">="&EDATE(Maturity!$B$2,3),'Report'!$L$2:$L$1200,"<"&EDATE(Maturity!$B$2,12))

Where: "H" is the values I'm trying to add
"L" is the column with dates
"B" is the reference date.

and I'm getting 0 for every value which doesn't make any sense.


These are the categories I'm trying to fill...

Three Months or Less
Over three months through 12 months
Over one year through 3 years
Over 3 years through 5 years
Over 5 years through 15 years
Over 15 years

Thank you all for your help...

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[daddylonglegs]

Sorry, my example wasn't very good - for past dates you probably need to use a negative number in EDATE, so if Maturity!$B$2 is a current date then for summing between 12 and 3 months before that date try

=SUMIFS('Report'!$H$2:$H$1200,'Report'!$L$2:$L$1200,">="&EDATE(Maturity!$B$2,-12),'Report'!$L$2:$L$1200,"<"&EDATE(Maturity!$B$2,-3))