Sum cells in a row containing >0, then stop after summing 15 cells >0

[Batonman]

Hi folks,

I'm looking for a formula that will sum cells in a row that contain a number >0. Some cells in the row will contain 0 and must be ignored. That's the easy but:

(1) I need the formula to stop once its identified and summed 15 cells that are >0 and​

​

(2) I need it to count back so that the data is from the most recent weeks 15 weeks.​

The row in question in the attached workbook is Gross Pay.

Thank you in advance,

Batonman

 

[p45cal]

(1) I need the formula to stop once its identified and summed 15 cells that are >0 and
(2) I need it to count back so that the data is from the most recent weeks 15 weeks.

These aren't compatible, it's either one or the other; 15 non-zero values go back 48 or so weeks in your data.

The attached has formulae for the last (rightmost) 15 non-zero values.
In cell DF10 there's

=LET(b,B10:DD10,a,FILTER(b,b<>0),SUM(DROP(a,,MAX(0,COUNT(a)-15))))

and in the cell to the right of it, a named lambda equivalent:

=SumLast15NonZero(B10:DD10)

 

[bosco_yip]

If you don't have Office 365, you could use this formula instead

In DF6, array ("Ctrl+Shift+Enter") formula copied down :

=IFERROR(1/(1/SUMIF(DD6:INDEX(B6: DD6,LARGE(IF(B6: DD6>0,COLUMN(B6: DD6)-COLUMN(A6)),15)),">0")),"")

 

[Batonman]

These aren't compatible, it's either one or the other; 15 non-zero values go back 48 or so weeks in your data.

The attached has formulae for the last (rightmost) 15 non-zero values.
In cell DF10 there's

=LET(b,B10:DD10,a,FILTER(b,b<>0),SUM(DROP(a,,MAX(0,COUNT(a)-15))))

and in the cell to the right of it, a named lambda equivalent:

=SumLast15NonZero(B10:DD10)

Thank you so much for getting back to me. I'll give it a go.

 

[Batonman]

If you don't have Office 365, you could use this formula instead

In DF6, array ("Ctrl+Shift+Enter") formula copied down :

=IFERROR(1/(1/SUMIF(DD6:INDEX(B6: DD6,LARGE(IF(B6: DD6>0,COLUMN(B6: DD6)-COLUMN(A6)),15)),">0")),"")

View attachment 80749

...many thanks - plenty to think about and I appreciate your help.