Count the first non-zero Consecutive Cells until another 0 appears

[tuqiuchiaa]

Hi,
I'm trying to figure out a way to count the number of cells in the first non-zero Consecutive cells.
Been reading all the posts about consecutive cells posts, but couldn't get anything working.
Could anyone help? Thanks in advance!
The desired results are listed below

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]32[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]231[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

A possible formula solution

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Val1​

[/TD]
[TD]

Val2​

[/TD]
[TD]

Val3​

[/TD]
[TD]

Val4​

[/TD]
[TD]

Val5​

[/TD]
[TD]

Val6​

[/TD]
[TD][/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

20​

[/TD]
[TD]

32​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

10​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

231​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

4​

[/TD]
[TD]

6​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in H2 copied down
=IFERROR(INDEX(FREQUENCY(IF(A2:F2<>0,COLUMN(A2:F2)),IF(A2:F2=0,COLUMN(A2:F2))),MATCH(1,--(FREQUENCY(IF(A2:F2<>0,COLUMN(A2:F2)),IF(A2:F2=0,COLUMN(A2:F2)))>0),0)),0)
Ctrl+Shift+Enter

M.

 

[Rick Rothstein]

I think you need to add some error handling code in there. That function blows up if all the cells are 0 (it returns the #VALUE ! error).

Good catch... I forgot about that being a real possibility. The fix is easy... we just need to make sure the Split function has something to split on, so I simply concatenate a blank onto the end of the result from the Trim operation...

[table="width: 500"]
[tr]
	[td]Function Consec(myRange As Range) As Long
  Consec = Len(Split(Application.Trim(Join(Evaluate("IF(" & myRange.Address & "=0,"" "",""X"")"), "")) [B][COLOR="#FF0000"]& " "[/COLOR][/B])(0))
End Function[/td]
[/tr]
[/table]

 

[Aladin Akyurek]

Aladin,

I came across an issue with this solution.
It works fine as long as there are BOTH zeroes and numbers.
But, if all six cells are 0, it will return a 6 (instead of 0).
And if all six cells are numbers other than 0, it will return 0 (instead of 6).

I assumed the usual, hastily as it appears... Marcelo has a formula proposal for the case, though.