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

A	B	C	D	E	F		Result
2	1	2	0	0	0		3
0	0	20	32	0	0		2
0	0	1	10	0	1		2
1	0	231	2	2	1		1

<tbody>
</tbody>

 

[Marcelo Branco]

A possible formula solution

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Val1​	Val2​	Val3​	Val4​	Val5​	Val6​		Result​
2​	2​	1​	2​	0​	0​	0​		3​
3​	0​	0​	20​	32​	0​	0​		2​
4​	0​	0​	1​	10​	0​	1​		2​
5​	1​	0​	231​	2​	1​	1​		1​
6​	1​	0​	4​	6​	0​	0​		1​
7​	0​	0​	0​	0​	0​	0​		0​

<tbody>
</tbody>

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.