Excel: Dynamic Sum offset when not zero but needs to stop until today

Question

I was struggling how to limit the range of my formula when a certain month like today's month is reached. In my formula it continues summing up the next four month after todays months. As in my example, I want to sum up the first 4 months after zero but only until today's month is reached. If there are less than 4 month lets say 3 months remaining until today's month then it should only add the remaining three month. I need to limit the width but it should be always four and only it should become less than four month when it is less then current month.

Jan	Feb	Mar	Apr	Jun	Jul	Aug	Oct	Nov	Dec
0	0	2	3	4	5	6	7	2	3	---> 1st period= 14 2nd period= 13
0	0	0	0	0	0	2	1	4	2	---> 1st period= 3 2nd Period= 0

Better in the picture below

For instance

For the first four months:

=IFERROR(SUM(OFFSET(INDIRECT(CELL("address",
INDEX(A2:N2,MATCH(TRUE,INDEX(A2:N2<>0,),0)))),0.0.1.4))," ")

For the second four months:

=IFERROR(SUM(OFFSET(INDIRECT(CELL("address",
INDEX(A2:N2,MATCH(TRUE,INDEX(A2:N2<>0,),0)))),0.0.1.8))
-SUM(OFFSET(INDIRECT(CELL("address",
INDEX(A2:N2,MATCH(TRUE,INDEX(A2:N2<>0,),0)))),0.0.1.4))," ")

Both formulas go beyond the current month and capture all the date beyond like Nov, Dec etc. and I don't know how to keep it at 4 months and only adjust it when the remaining months are less than four months

Thank you so much for your help!

Answer 1

For first period in first row use:

=SUMPRODUCT($A2:$L2*(COLUMN($A2:$L2)>=AGGREGATE(15,6,COLUMN($A2:$L2)/($A2:$L2>0),1)+4*(COLUMN(A2)-1))*(COLUMN($A2:$L2)<AGGREGATE(15,6,COLUMN($A2:$L2)/($A2:$L2>0),1)+4*(COLUMN(A2)))*($A$1:$L$1<TODAY()))

and copy to the right and down

the formula will work if the first row contains actually formatted dates instead of text

---

Explanation for first period in first row:

1. $A2:$L2 returns array of values in given range:

   {0,0,1,3,4,6,5,5,5,10,4,3}

2. AGGREGATE(15,6,COLUMN($A2:$L2)/($A2:$L2>0),1) returns smallest column number which value is greater than 0:

   3

3. 4*(COLUMN(A2)-1) becomes 4*(1-1) and returns:

   0

4. so expression AGGREGATE(15,6,COLUMN($A2:$L2)/($A2:$L2>0),1)+4*(COLUMN(A2)-1) becomes 3+0 and returns:

   3

5. comparision COLUMN($A2:$L2)>=AGGREGATE(15,6,COLUMN($A2:$L2)/($A2:$L2>0),1)+4*(COLUMN(A2)-1) becomes {1,2,3,4,5,6,7,8,9,10,11,12}>=3 and returns array of booleans:

   {F,F,T,T,T,T,T,T,T,T,T,T,T}

6. analogically second expression COLUMN($A2:$L2)<AGGREGATE(15,6,COLUMN($A2:$L2)/($A2:$L2>0),1)+4*(COLUMN(A2)) becomes {1,2,3,4,5,6,7,8,9,10,11,12}<7 and returns:

   {T,T,T,T,T,T,F,F,F,F,F,F}

7. third expression $A$1:$L$1<TODAY() compares dates in first row with today's date and in october 27 will return:

   {T,T,T,T,T,T,T,T,T,T,F,F}

8. so final expression we get {0,0,1,3,4,6,5,5,5,10,4,3}*{F,F,T,T,T,T,T,T,T,T,T,T,T}*{T,T,T,T,T,T,F,F,F,F,F,F}*{T,T,T,T,T,T,T,T,T,T,F,F} which results into:

   {0,0,1,3,4,6,0,0,0,0,0,0}

9. from which SUMPRODUCT summs up:

   14