# countif # two consective cells have zeros

#### cpicsonly

##### New Member
I have data in excel like this
 A B C D E F G H I J K L M N 0 0 0 0 22 55 7 3 0 0 0 5 44 5

<tbody>
</tbody>

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.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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).

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.

@cpicsonly

If you don't want treat empty cells as 0 (what IF does), try to also run an ISNUMBER test:
Rich (BB code):
``````=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

If you don't want treat empty cells as 0 (what IF does), try to also run an ISNUMBER test:
Rich (BB code):
``````=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))
``````

Even better !
thanks

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))

Replies
4
Views
336
Replies
3
Views
374
Replies
3
Views
93
Replies
16
Views
598
Replies
3
Views
781

1,206,759
Messages
6,074,773
Members
446,086
Latest member
daywi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back