XL Formula Help (count cells with consecutive zeroes)

[maxchunflin76]

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

[#2]

@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

[#3]

@maxchunflin76 

If such variant is considered

it could be

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

[#4]

Yes, that is better

[#5]

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

[#6]

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