XL Formula Help (count cells with consecutive zeroes)

%3CLINGO-SUB%20id%3D%22lingo-sub-3081098%22%20slang%3D%22en-US%22%3EXL%20Formula%20Help%20(count%20cells%20with%20consecutive%20zeroes)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3081098%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there!%20Asking%20for%20a%20bit%20of%20help%20as%20I%20am%20stomped.%20Need%20a%20formula%20that%20helps%20me%20count%20%22how%20many%20consecutive%20cells%22%20to%20the%20%22left%22%20include%20zeroes%20(until%20a%20positive%20number%20is%20present)...%3C%2FP%3E%3CTABLE%20width%3D%22711%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2283%22%3ECOMPANY%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EJul-21%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EAug-21%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ESep-21%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EOct-21%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ENov-21%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EDec-21%3C%2FTD%3E%3CTD%20width%3D%22244%22%3EHow%20many%20months%20with%20zero%20billings%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECOMPANY%20A%3C%2FTD%3E%3CTD%3E3513%3C%2FTD%3E%3CTD%3E13513%3C%2FTD%3E%3CTD%3E1351%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECOMPANY%20B%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECOMPANY%20C%3C%2FTD%3E%3CTD%3E35138%3C%2FTD%3E%3CTD%3E31351%3C%2FTD%3E%3CTD%3E651351%3C%2FTD%3E%3CTD%3E35131%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3081098%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3081380%22%20slang%3D%22en-US%22%3ERe%3A%20XL%20Formula%20Help%20(count%20cells%20with%20consecutive%20zeroes)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3081380%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1290194%22%20target%3D%22_blank%22%3E%40maxchunflin76%3C%2FA%3E%26nbsp%3B%20Hi.%3C%2FP%3E%3CP%3EYou%20will%20benefit%20from%20using%20XMATCH%2C%20available%20in%20later%20versions%20of%20Excel.%3C%2FP%3E%3CP%3EThe%20last%20paramater%20-1%20makes%20it%20search%20from%20right%20to%20left.%3C%2FP%3E%3CP%3EHere%2C%20applied%20using%20LET*.%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(firstValue%3BXMATCH(0%2C1%3BA2%3AG2%3B1%3B-1)%3B%0A%20%20%20%20%20valueColumns%3BCOLUMNS(B%3AG)%3B%0AnoOfZeros%3BfirstValue-1%3B%0Aoutput%3BIF(noOfZeros%3D0%3BvalueColumns%3BnoOfZeros)%3B%0Aoutput%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bosinander_0-1643389558044.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343381iB263707C0D0AD944%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bosinander_0-1643389558044.png%22%20alt%3D%22bosinander_0-1643389558044.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B*%26nbsp%3BYou%20may%20switch%20the%20last%20output%20to%20eg%20firstValue%20to%20display%20the%20result%20from%20the%20formulas%20different%20parts%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3082652%22%20slang%3D%22en-US%22%3ERe%3A%20XL%20Formula%20Help%20(count%20cells%20with%20consecutive%20zeroes)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3082652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1290194%22%20target%3D%22_blank%22%3E%40maxchunflin76%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20such%20variant%20is%20considered%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20753px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343434iEB8AE4BE732BEB59%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DCOLUMNS(%20B2%3AG2%20)%20-%20IFNA(%20LOOKUP(%202%2C%201%2FB2%3AG2%2C%20SEQUENCE(%2CCOLUMNS(%20B2%3AG2%20)%20)%20)%2C%200%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3089745%22%20slang%3D%22en-US%22%3ERe%3A%20XL%20Formula%20Help%20(count%20cells%20with%20consecutive%20zeroes)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3089745%22%20slang%3D%22en-US%22%3EYes%2C%20that%20is%20better%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi there! Asking for a bit of help as I am stomped. Need a formula that helps me count "how many consecutive cells" to the "left" include zeroes (until a positive number is present)...

COMPANYJul-21Aug-21Sep-21Oct-21Nov-21Dec-21How many months with zero billings?
COMPANY A35131351313510003
COMPANY B0000006
COMPANY C351383135165135135131002
5 Replies

@maxchunflin76  Hi.

You will benefit from using XMATCH, available in later versions of Excel.

The last paramater -1 makes it search from right to left.

Here, applied using LET*. 

=LET(firstValue;XMATCH(0,1;A2:G2;1;-1);
     valueColumns;COLUMNS(B:G);
noOfZeros;firstValue-1;
output;IF(noOfZeros=0;valueColumns;noOfZeros);
output
)

bosinander_0-1643389558044.png

 

 * You may switch the last output to eg firstValue to display the result from the formulas different parts

@maxchunflin76 

If such variant is considered

image.png

it could be

=COLUMNS( B2:G2 ) - IFNA( LOOKUP( 2, 1/B2:G2, SEQUENCE(,COLUMNS( B2:G2 ) ) ), 0 )
Yes, that is better

@maxchunflin76 

Just in case anyone is interested in a 365 beta release solution ...

= REDUCE(6,SEQUENCE(6),
    LAMBDA(acc,p, 
      IF(INDEX(amount,,p)>0,6-p,acc)
    )
  )

 

...or for versions pre dynamic arrays (pda), like Excel 2016 and others before 365 that not yet had functions like sequence().

=COLUMNS(B:G)-IFERROR(MATCH(2,1/B2:G2),0)