Sum of First 12 Non-Zero Values + If less than 12 Sum of Total

[GP24]

Hi - I am trying to sum the first 12 Values in a set that aren't Zero. I found a great formula from @ Dr. Steele that works great, but the problem I have is that some of my rows have less than 12 non-zero values, so for those I would like the values from the first non-zero for the next 11 rows (12 total). Below is the formula I found. I did also have to add <>0 as there is a negative task amount I wanted the formula to work on. Any ideas?


=SUM(D1:INDEX(D1:AY1,1,SMALL(IF(D1:AY1>0,COLUMN(D:AY)-COLUMN(D:D)+1),B2)))when invoked with CtrlShiftEnter and B2=12

 

[jasonb75]

Try this one

=SUM(IFERROR(INDEX(1:1,AGGREGATE(15,6,COLUMN(D1:AY1)/(D1:AY1>0),SEQUENCE(,12))),0))

 

[GP24]

Try this one

=SUM(IFERROR(INDEX(1:1,AGGREGATE(15,6,COLUMN(D1:AY1)/(D1:AY1>0),SEQUENCE(,12))),0))

Thank you! This seems to work well, except in the scenario where the values are a negative number, is there a way to account for that?

 

[GP24]

Thank you! This seems to work well, except in the scenario where the values are a negative number, is there a way to account for that?

Found it - sorry I overlooked that part of the formula. That works perfectly thank you.

For reference this is what I used.

=SUM(IFERROR(INDEX(197:197,AGGREGATE(15,6,COLUMN(X197:AU197)/(X197:AU197<>0),SEQUENCE(,12))),0))