1

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

2 Answers 2

0

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

2
  • 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
    – Artur
    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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.