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]

And which Office version do you use?

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[cgsierra]

And which Office version do you use?

Excel for Microsoft office 365

 

[cgsierra]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I will. Thank you!

 

[Anthony47]

The simplest formula I could imagine is, 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,SEQUENCE(COLUMNS(dBlock)-iPos-zStr+1,1,iPos+zStr)))*(iPos>0))

BD2 is used to set how many consecutive Zero have to be found to trigger the Summing; if it's empty then this will be defaulted to 12

Then copy BC2:BD2 down

Probably a macro could get the result quicker, but the presence of Office 365 and its nice features switched me to the formula...

Note that the formula SUMs the values (as is written in the body of the message), and not COUNT them (as is written in the title of the thread)

 

[cgsierra]

The simplest formula I could imagine is, 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,SEQUENCE(COLUMNS(dBlock)-iPos-zStr+1,1,iPos+zStr)))*(iPos>0))

BD2 is used to set how many consecutive Zero have to be found to trigger the Summing; if it's empty then this will be defaulted to 12

Then copy BC2:BD2 down

Probably a macro could get the result quicker, but the presence of Office 365 and its nice features switched me to the formula...

Note that the formula SUMs the values (as is written in the body of the message), and not COUNT them (as is written in the title of the thread)

Thank you very much for your help. I tried the formula and it returns zero’s for all rows, even when I change the number in column BD to match the number of cells with consecutive zeros. Also, how can I change the formula so that it sums the revenue after 12 zeros or more (as opposed to exactly 12 zeros)?
Thanks again for your kind help

 

[Anthony47]

The formula checks for "12 or more consecutive Zeros", and should search for the last of these occourrences.

My demo file can be downloaded here: CGSIERRA_Demo_c20607.xlsx

 

[cgsierra]

The formula checks for "12 or more consecutive Zeros", and should search for the last of these occourrences.

My demo file can be downloaded here: CGSIERRA_Demo_c20607.xlsx

Thank you! It seems that I had to save the file for the calculations to work. There are some instances however, where I get a #CALC! error message. Not sure why. Have you come across that?

 

[cgsierra]

Thank you! It seems that I had to save the file for the calculations to work. There are some instances however, where I get a #CALC! error message. Not sure why. Have you come across that?

it seems like it is happening when the last 12 months or more have zeros in them