Count the Number of Gaps Between Numbers in a Row

[dskexcel]

Hi - I need to count the number of times a single or multiple set of columns contain "0" before the next column, which would contain a number. Probably not doing great describing it, but in the below example, I would want the formula to bring a result of 3:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]56[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]656[/TD]
[TD]654[/TD]
[TD]653[/TD]
[TD]32[/TD]
[TD]32[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]65[/TD]
[TD]0[/TD]
[TD]45[/TD]
[TD]45[/TD]
[/TR]
</tbody>[/TABLE]

So, I need to count the number of sets or number of single 0's within the range. In this case, there are 3 (in red). I also only need to count the sets of 0 after the first >0 cell in the range. So in the example above, I need to count the sets after the 3rd column or the cell with the # 12.

If i could avoid using an array that would be ideal, as I need to calculate this for 20K rows.

Thanks!

 

[dskexcel]

One idea I have is "if cell a>0 AND a+b=a, then count it." Any way to do this for the whole range?

 

[Fennek]

Hello,

why is the first and second column excluded?

regards

 

[dskexcel]

Because I only need to start counting the sets after the first positive number in the range.

 

[Fennek]

Hi,

my fun is to type some letters in nodepad:

sub iFen()
j=1
do until cells(1,j) > 0
j=j+1
loop
set rng = range(cells(1,j), cells(1, columns.count).end(xltoleft))
rng.replace 0, "a"
msgbox "number of 0 blocks: " & rng.specialcells(2,1).areas.count
rng.replace  "a", 0
end sub

Some issues for debugging are likely.

regards

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(INDEX(A2:U2,MATCH(TRUE,A2:U2>0,0)):U2=0,COLUMN(INDEX(A2:U2,MATCH(TRUE,A2:U2>0,0)):U2)),IF(1-(INDEX(A2:U2,MATCH(TRUE,A2:U2>0,0)):U2=0),COLUMN(INDEX(A2:U2,MATCH(TRUE,A2:U2>0,0)):U2))),1))

 

[dskexcel]

Great, thanks to both!

 

[admiral100]

Hi,

It's an array one....so Ctrl+Shift+Enter NOT just Enter

For non-zero sets

=SUM(IFERROR(FREQUENCY(IF(A1:U1<>0,COLUMN(A1:U1)),IF(A1:U1<>0,0,COLUMN(A1:U1)))^0,0))

For zero sets

=IF(A1=0,SUM(IFERROR(FREQUENCY(IF(A1:U1=0,COLUMN(A1:U1)),IF(A1:U1=0,0,COLUMN(A1:U1)))^0,0))-1,SUM(IFERROR(FREQUENCY(IF(A1:U1=0,COLUMN(A1:U1)),IF(A1:U1=0,0,COLUMN(A1:U1)))^0,0)))

 

[István Hirsch]

Give this a try, $V$2 should be left blank:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2	0	0	12	0	5	0	0	0	3	0	22	1	2	0	22	1	1	2	0	0	0		5
Sheet1