SUM between upper and lower ranges of cumulative figures

Dear All,

This is my first post to the forum, so I hope I have followed all the rules.

I hope it is possible that you could help me with the following problem. This has been bugging me for some time now.

I have a table that lists days spent on an activity by month (D7:D17). The next column keeps a running total of these days (E7:E17). From the cumulative totals (E7:E17) I need to split the days into categories of 0 to 45 days, 46 to 60 days and finally 61+ days.

I have managed to use formulae to calculate the 0 to 45 days (F7:F17) and 60+ days (N7:H17), based on the cumulative column, but I'm having trouble with the 46 to 60 days formula (G7:G17). I'm hoping a formula can be created that looks at the cumulative days already spent and if the cumulative days up to that month are over 45 days but less than 60, it will add in the days within that range for the month in question.

I know that the results in the following cells should be:
G7:G9 = all 0
G10 = 2
G11 = 0
G12 = 13
G13:G17 = all 0
Total (cell G18)= 15 days

At the top of the table there are some input boxes (E2:H4). I'd hope that the formula can reference these cells so if the need to change the ranges occurs, I can simply change these cells.

I have enclosed the Excel file with the table.

Any help that can be offered would be most appreciated.

Regards,

David

Cumulative day ranges.xlsx

Dear etaf,

Thanks for looking into this. Please let me know if you need any further info.

Thank you etaf, that works perfectly. I shall now go and try and understand how you did it.


Also, thank you for the swift response.

Dear etaf,

I've been playing with the ranges, changing the 46 to 60 days to 46 to 75 days. Once the 75 days is input in cell G3, the total for the 46 to 75 days column totals 32. This should be 30 days (28 days in cell G12). It appears to be adding addtional days and I can't see why this is so.

Do you have any recommendations to solve this?

Your help is most appreciated.

=IF(NOT(ISNUMBER(E11)),0,IF(AND(SUM($D$7:D12)>=$G$2,SUM($D$7:D12)<$G$3,E12-E11>0),E12-($G$2-1),IF(E12>=$G$3,IF(E11<$G$3,($G$3)-E11,0),0)))

I changed an = to , because - if the value in E = the upperlimit - it was taken the lower limit

following the rules
=75-47 = 28

so it maybe the actual counting of the days - so if day 75 is to be counted -

it maybe the whole thing needs to be rethought out

so on the lower limit
,E7-($G$2-1)

So we count the lower limit day 46 as 1 ($G$2-1)

if i then add 1 to the upper limit to include the 75 day
($G$3+1)-E11
it does not work for your 13 example

I guess these calculations maybe all wrong

Hi etaf,

I've had a look through your amazing formula and I think I've found a quick fix. I'm sure there is a much better way of doing it, but I'm hoping this inelegant add in will suffice.

Using your original formula, added in the following:

=IF(NOT(ISNUMBER(E6)),0,IF(AND(SUM($D$7:D7)>=$G$2,SUM($D$7:D7)<=$G$3,E7-E6>0),E7-($G$2-1)-(SUMG6:G7),IF(E7>$G$3,IF(E6<$G$3,($G$3)-E6,0),0)))

I believe that E7-($G$2-1) is taking the whole range between 46 and 60 days. By adding in the -(SUMG6:G7) this takes out those days within the 46 to 60 days that have already been counted. I copied down the column and it seems to work.

Many thanks for your help with this problem.

your welcome, glad to have got you started
hopefully it works for all variations