Count all cell after 12 cell with zeros

[cgsierra]

Hello,
I have a table that has the column titles as months, Jan 2018 through April 2022 and the y axis (row titles) as customers (ie 10,000 customers). The data is revenue.
I need a formula that I can place on the column that follows April 2022 that sums the Revenue after 12 consecutive cells (1 year of inactivity) with zeroes in them for each client. This period of 12 consecutive zeroes is different for each client and some don’t even have such period of inactivity. Your help would be greatly appreciated
Carlo

 

[Anthony47]

It seems that I had to save the file for the calculations to work

That shouldn't be necessary, in my knowledge

Does the #CALC error appear on specific rows of data? Can you share a sample test file? My test file is the one I sherd, ie very limited

 

[Anthony47]

Your answer arrived before the question...

So the question is: in case that the missing revenues are at the end of the period, which is the correct result to get? Would "0" be correct?

 

[cgsierra]

Your answer arrived before the question...

So the question is: in case that the missing revenues are at the end of the period, which is the correct result to get? Would "0" be correct?

Yes, that is correct. Thank you!

 

[Rick Rothstein]

I am a little confused at what you want. Would it be fair to say you want the sum of values in a row if and only if there are 12 consecuted 0's anywhere in that row? Or do the 12 consecutive 0's have to be the last 12 entries on the row?

Also, is this a real Excel table or just a bunch of cells that you think of as a table? If a real Excel table, what are the table and the column header names?

 

[cgsierra]

I am a little confused at what you want. Would it be fair to say you want the sum of values in a row if and only if there are 12 consecuted 0's anywhere in that row? Or do the 12 consecutive 0's have to be the last 12 entries on the row?

Also, is this a real Excel table or just a bunch of cells that you think of as a table? If a real Excel table, what are the table and the column header names?

Hello Rick,
I’m looking for the sum of values in a row that follow 12 or more consecutive 0’s. If there is more then one set of 12+ consecutive 0’s in the same row, the sum should be based on the values that follow the last set. If the last set goes all the way to the current month, then the result should be zero (ie the last 12+ months have been 0’s). If there are no sets of 12+ consecutive 0’s in the row then the result should be zero as well.
I am actually using a pívot table for this so the column headers will be the months starting with 1/31/18, second would be 2/28/18…all the way to current month (in this case for now 4/30/22) and the row headers will be the name of each client ( over 10,000 clients). Hope this helps

 

[Anthony47]

To deal with the 0's potentially arriving to the last column of data we can modify the SEQUENCE formula, encapsulating it in a IFERROR clause.
Thus, in BC2:

=LET(lStr,BD2,zStr,IF(lStr=0,12,lStr),dBlock,B2:BA2,dString,TEXTJOIN("",TRUE,IF(dBlock<>0,0,1)),iPos,MAX(IFERROR(FIND(REPT("1",zStr),dString,ROW(INDIRECT("1:"&LEN(dString)))),0)),SUM(INDEX(dBlock,1,IFERROR(SEQUENCE(COLUMNS(dBlock)-iPos-zStr+1,1,iPos+zStr),COLUMNS(dBlock))))*(iPos>0))

A few information to customize the formula:
-the number of Zeros is defined by lStr,BD2; you may change BD2 (cell address) with any other excel notation (generally a value or a reference to a cell)
-the default number of Zero is defined by zStr, IF(lStr=0,12,lStr) You may replace that 12 with your default value
-the Columns to examine are defined by dBlock,B2:BA2 If your "data block" has a different position then change this block
The rest of the formula works on the above settings

 

[cgsierra]

To deal with the 0's potentially arriving to the last column of data we can modify the SEQUENCE formula, encapsulating it in a IFERROR clause.
Thus, in BC2:

=LET(lStr,BD2,zStr,IF(lStr=0,12,lStr),dBlock,B2:BA2,dString,TEXTJOIN("",TRUE,IF(dBlock<>0,0,1)),iPos,MAX(IFERROR(FIND(REPT("1",zStr),dString,ROW(INDIRECT("1:"&LEN(dString)))),0)),SUM(INDEX(dBlock,1,IFERROR(SEQUENCE(COLUMNS(dBlock)-iPos-zStr+1,1,iPos+zStr),COLUMNS(dBlock))))*(iPos>0))

A few information to customize the formula:
-the number of Zeros is defined by lStr,BD2; you may change BD2 (cell address) with any other excel notation (generally a value or a reference to a cell)
-the default number of Zero is defined by zStr, IF(lStr=0,12,lStr) You may replace that 12 with your default value
-the Columns to examine are defined by dBlock,B2:BA2 If your "data block" has a different position then change this block
The rest of the formula works on the above settings

Thank you so much! This works perfectly
One last related question, in a separate calculation, how can I get the cell “address” of the last zero of the last 12+ consecutive set of 0s?

 

[Anthony47]

To get the "column number" (relative to the data block) just stop the formula a little bit earlier:

=LET(lStr,BD2,zStr,IF(lStr=0,12,lStr),dBlock,B2:BA2,dString,TEXTJOIN("",TRUE,IF(dBlock<>0,0,1)),iPos,MAX(IFERROR(FIND(REPT("1",zStr),dString,ROW(INDIRECT("1:"&LEN(dString)))),0)),IF(iPos>0,iPos+zStr-1,0))

In the demo file this is the Blue area

If you need the cell address

=LET(lStr,BD2,zStr,IF(lStr=0,12,lStr),dBlock,B2:BA2,dString,TEXTJOIN("",TRUE,IF(dBlock<>0,0,1)),iPos,MAX(IFERROR(FIND(REPT("1",zStr),dString,ROW(INDIRECT("1:"&LEN(dString)))),0)),db0,INDEX(dBlock,1,1), IF(iPos>0,ADDRESS(ROW(db0),iPos+zStr-2+COLUMN(db0),4),0))

In the demo file this is the orange area

Note that these formulas share column BD with the previous formula to make sure the two results are consistent, but this is my choice for my demo file you can use whichever parameter you need

 

[cgsierra]

Much appreciated!!

 

[Rick Rothstein]

This formula to get the sum you asked for appears to be more compact (by about 86 characters) than your current formula...

=LET(Zeros,REPT(0,12),Cat,CONCAT(0+(B2:BD2<>0)),Col,COLUMN(B2:BD2),IfIsZeros,IF(MID(Cat,Col,12)=Zeros,Col),IF(ISNUMBER(FIND(Zeros,Cat)),IF(MAX(IfIsZeros),SUM(INDEX(B2:BD2,MAX(IfIsZeros)):BD2),0),0))