Google Sheets – How to Count a Streak of Consecutive Numbers

google sheets

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"?

enter image description here

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

Best Answer

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 :

enter image description here

Related Topic