Stop counting where 0 is found in a row out of 20 cells

[KapardhiSarma]

Hi Team,

I have a requirement that in a row of 20 cells if the 1st cell is 0 it should show as 0, and if the number is 1 it should start counting till it find 0 by end of all 20 cells in a row.

Attaching an example for your reference.
Explained in column 'W' of the attachment about the end result.

Thanks a lot for your help in advance.

 

[Somendra Misra]

Hi Kapardhi,

Try below array formula in W2 and copy down:

=IF(A2=0,0,IF(A2=1,COUNT(OFFSET(A2,,,,MIN(IFERROR(MATCH(0,A2:V2,0)-1,999999),SUM(IF(A2:V2<>0,1,0)))))))

Enter with Ctel+Shift+Enter.

Regards,

 

[Debraj]

Hi KapardhiSarma!

Can you please check with this one too..

=MATCH(0,$A2:$V2,0)-1

 

[Somendra Misra]

@Debraj dada

would like to modify your formula for the case where there is no 0's and all 1, based on the data in sample file with only 1 & 0's.

=IFERROR(MATCH(0,$A2:$V2,0)-1,COUNT($A2:$V2))

Regards,

 

[Debraj]

OOPS.. Nice catch.. @Somendra Misra

 

[Somendra Misra]

@KapardhiSarma

If both formula works for you than please use @Debraj formula, as it is clean, easy and do not uses any volatile function which will make it faster.

@Somendra Misra

You need to start doing Excel Yoga to free your mind. "There is no Complexity" on the similar line of "There is no spoon from MATRIX"

Regards,

 

[KapardhiSarma]

Thank you Deb and Somendra...This helped with my requirement.

 

[Somendra Misra]

Glad you solve it & thanks for the feedback.

Welcome back anytime!!

Regards,