CODEWITHSUNDEEP
excelexcel-formula
QHarr
QHarr

Reputation: 84455

Excel: How to sum a column to date from a specified date where date ranges are involved

I'm sure i have seen this done with a short formula but i am struggling to remember how to do it.

I am trying to find where a date falls in an interval and sum another column, from that point in the interval, to the end of all specified date intervals.

So, in the image below the intervals are in columns D and E and the date to find is in cell F1 i.e. 12/12/2016.

I want to find where 12/12/2016 falls within the ranges and sum column F accordingly i.e. 12/12/2016 - 13/12/2016 (2 days) and then all intervals after will be 2 + 6+2+1+3 = 14. I am returning this result in cell J14.

Data

I have used the idea of histograms to calculate this currently, but the formula is large and unwieldy, and i just know i have seen a similar question, somewhere on SO but can't find it, that does this with SUMPRODUCT and OFFSET only. I guess FREQUENCY could also be used.

So what i have currently is: 

=SUMPRODUCT(OFFSET(F6,MATCH(TRUE,OFFSET(E6,0,0,COUNT(E6:E1048576),)>F1,0)-1,0,COUNTA(F6:F1048576),))-(F1-OFFSET(D6,MATCH(TRUE,OFFSET(E6,0,0,COUNT(E6:E1048576),)>F1,0)-1,,,))

Where, if i broke it down into stages, i find which bucket (range) has the target value:

={MATCH(TRUE,OFFSET(E6,0,0,COUNT(E6:E1048576),)>F1,0)}

I calculate the distance into the range with: 

=F1-OFFSET(D6,H13-1,,,)

And i sum from this point until the end of the range with:

=SUMPRODUCT(OFFSET(F6,H13-1,0,COUNTA(F6:F1048576),))-I13

So, can anyone help me with a shorter more efficient formula please?

Data:

| Start of measurement | 12/12/2016 |                |
|----------------------|------------|----------------|
|                      |            |                |
|                      |            |                |
|                      |            |                |
| From                 | To         | Number of days |
| 13/11/2016           | 17/11/2016 | 5              |
| 10/12/2016           | 13/12/2016 | 4              |
| 03/02/2017           | 08/02/2017 | 6              |
| 06/12/2017           | 07/12/2017 | 2              |
| 09/12/2017           | 09/12/2017 | 1              |
| 12/12/2017           | 14/12/2017 | 3              |

Upvotes: 0

Views: 141

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

How about this?

=SUMIFS(F6:F11,D6:D11,">="&F1)+MINIFS(E6:E11,E6:E11,">="&F1)-F1+1

The SUMIFS() gives the 6+2+1+3 part and the MINIFS() - F1 + 1 gives the 2 part.

Note that the ___IFS() functions are more recent and not available in older versions of Excel.

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26640

This should work for you:

=SUMIF(E6:E11,">="&F1,F6:F11)-F1+INDEX(D6:D11,MATCH(F1,D6:D11))

Upvotes: 1

Related Questions