Forum Discussion

maxchunflin76's avatar
maxchunflin76
Copper Contributor
Jan 28, 2022

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 J...
excel
Formulas and Functions
bosinander's avatar
bosinander
Iron Contributor
Jan 28, 2022

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
)

 

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

Resources