# XL Formula Help (count cells with consecutive zeroes)

Occasional Visitor

# XL Formula Help (count cells with consecutive zeroes)

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)...

 COMPANY Jul-21 Aug-21 Sep-21 Oct-21 Nov-21 Dec-21 How many months with zero billings? COMPANY A 3513 13513 1351 0 0 0 3 COMPANY B 0 0 0 0 0 0 6 COMPANY C 35138 31351 651351 35131 0 0 2
5 Replies

# Re: XL Formula Help (count cells with consecutive zeroes)

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
)``````

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

# Re: XL Formula Help (count cells with consecutive zeroes)

If such variant is considered

it could be

``=COLUMNS( B2:G2 ) - IFNA( LOOKUP( 2, 1/B2:G2, SEQUENCE(,COLUMNS( B2:G2 ) ) ), 0 )``

# Re: XL Formula Help (count cells with consecutive zeroes)

Yes, that is better

# Re: XL Formula Help (count cells with consecutive zeroes)

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)
)
)``````

# Re: XL Formula Help (count cells with consecutive zeroes)

...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)``