 # Count number of zeros that are within a range of consecutive zeros

• I have rows of data containing zeros and ones. I need a formula that counts the number of zeros in a particular row, that are contained within at least 18 consecutive zeros. Some rows contain 2 ranges of at least 18 consecutive zeros so I would want to total that.

There are 48 columns per row. Please see example attached - column AW should show the answer.

Also posted here: https://www.mrexcel.com/forum/…ve-zeros.html#post4733795

## Files

• Re: Count number of zeros that are within a range of consecutive zeros

Try this UDF

Then in cell AW2 put this formula

=zerocount(A2:AV2)

and copy down as far as needed.

Note the count for Row 5 should be 43, not 42 as shown in your sample file.

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

• Re: Count number of zeros that are within a range of consecutive zeros

If you NEED an answer that does not use VBA at all, and have spare columns, this will do it:
Cell AY2 = "=IF(SUM(A2:R2)=0,IF(AX2>0,1,18),0)" fill across to CC2 and then down to bottom of table.
Then a simple "=SUM(AY2:CC2)" in AW2 (and filled down) will provide accurate counts.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!