Google Sheets: How to count a streak/consecutive occurrence of one number?

I am trying to count amount of consecutive occurrences of number 0 in a row.

``````A   B   C   D   E   F
5   0%  0%  0%  0%  0%
2   0%  0%  1%  0%  0%
``````

Formula in A1 and B1 counts how many consecutive 0 are in a row and stops if something else but 0 is encountered.

Currently I have this formula:

``````=if(B2+C2+D2+E2+F2=0,5,
if(B2+C2+D2+E2=0,4,
if(B2+C2+D2=0,3,
if(B2+C2=0,2,
if(B2=0,1,0)))))
``````

It works but I need to check 100+ cells in one row and I was wondering if there is a more elegant solution?

EDIT: As suggested below `=match(true,ArrayFormula(IF(TRANSPOSE(B2:F2)<>0,true,false)),0)` works perfectly. I've just added "-1" to get the sum of zeroes:

```````=match(true,ArrayFormula(IF(TRANSPOSE(B2:F2)<>0,true,false)),0)-1`
``````

Unfortunately I forgot that my data has "N/A" text in some cells. Is there a way to make that formula to ignore "N/A"?

It counts to 6 and stops, I want it to ignore "N/A" and get 8 as a result

First, transpose a single row :

``````=TRANSPOSE(B1:F1)
``````

Then with `IF` + `Arrayformula` convert to `true` and `false`

``````=ArrayFormula(IF(TRANSPOSE(B2:F2)<>0,true,false))
``````

Then use `=Match` to find the position of the first value :

``````=match(true,ArrayFormula(IF(TRANSPOSE(B2:F2)<>0,true,false)),0)
``````

You can add a specific answer when there's no match with `iferror`.

Proof :

• Thank you very much! Works perfectly. I've added "-1" so I get the amount of 0. Unfortunately, I completely forgot that my data has "N/A" in some cells. Is there a way to make your formula to ignore cells with "N/A" text ? i.imgur.com/NyBW4OU.png Jan 22, 2021 at 15:13
• Your N/A doesn't look like a `#N/A` from Google Sheets ? You can try to replace <>0 with >0 maybe ?
– pjmg
Jan 22, 2021 at 15:25

Many thanks to pjmg. After tweaking his solution I got the final formula:

``````=match(true,ArrayFormula(IF(TRANSPOSE(FILTER(B2:F2,B2:F2<>"N/A"))<>0,true,false)),0)-1
``````