Forum Discussion
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
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)
If such variant is considered
it could be
=COLUMNS( B2:G2 ) - IFNA( LOOKUP( 2, 1/B2:G2, SEQUENCE(,COLUMNS( B2:G2 ) ) ), 0 )- Yes, that is better 🙂
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
