countif # two consective cells have zeros

[cpicsonly]

I have data in excel like this
[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]22
[/TD]
[TD]55
[/TD]
[TD]7
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD]44
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]




I want to countif consecutive two cells have "SAY value" 0

So first 4 columns will return frequency function =2
if all columns given as cell range for formula, then frequency should be = 3
as at end only 3 columns have two consecutive zero cell.
How can i write formula. I tried if and array but making mistake.

 

[XOR LX]

Hi.

I believe there must be a simpler solution using COUNTIFS, though I can't seem to construct it quite right.

This array formula** should work:

=SUM(QUOTIENT(FREQUENCY(IF(Range=0,COLUMN(Range)),IF(Range<>0,COLUMN(Range))),2))

Substitute Range as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[cpicsonly]

Hi.

I believe there must be a simpler solution using COUNTIFS, though I can't seem to construct it quite right.

This array formula** should work:

=SUM(QUOTIENT(FREQUENCY(IF(Range=0,COLUMN(Range)),IF(Range<>0,COLUMN(Range))),2))

Substitute Range as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Dear sir @XOR LX
You are absolutely right. The formula worked perfectly without any change.

Now one more query plz. (Donot give me fish but teach me to grab ONE)

I removed quotient text from formula, it gives count of number of zeros in a row, which is higher than counted by frequency function alone. I checked info of quotient function, which returns integer from a ratio. But never came across division in above formula. Can u plz describe me the logic

plz.

 

[Aladin Akyurek]

@cpicsonly

If you don't want treat empty cells as 0 (what IF does), try to also run an ISNUMBER test:

=SUM(QUOTIENT(FREQUENCY(IF(ISNUMBER(A1:N1),IF(A1:N1=0,COLUMN(A1:N1))),
    IF(ISNUMBER(A1:N1),IF(1-(A1:N1=0),COLUMN(A1:N1)))),2))

 

[cpicsonly]

@cpicsonly

If you don't want treat empty cells as 0 (what IF does), try to also run an ISNUMBER test:

=SUM(QUOTIENT(FREQUENCY(IF(ISNUMBER(A1:N1),IF(A1:N1=0,COLUMN(A1:N1))),
    IF(ISNUMBER(A1:N1),IF(1-(A1:N1=0),COLUMN(A1:N1)))),2))

@Aladin
Even better !
thanks

 

[Aladin Akyurek]

@Aladin
Even better !
thanks

You are welcome. The following, again confirmed with control+shift+enter, might help to understand QUOTIENT...

=INT(SUM(FREQUENCY(IF(ISNUMBER(A1:N1),IF(A1:N1=0,COLUMN(A1:N1))),IF(ISNUMBER(A1:N1),IF(1-(A1:N1=0),COLUMN(A1:N1))))/2))