Does anyone know a simple way to count the number of blank cells between
non-blank cells? I have a column of data that in some cases has from zero to
five blank cells between non-blank ones. If there are not two blank cells
between the non-blank ones, it is an indication of a potential coding error
within the data source. I'm looking for an easy way to do my data scrub.

Any ideas would be helpful.

Thanks.

=COUNTBLANK(A1:A10)




"AndyH" wrote:

Does anyone know a simple way to count the number of blank cells between
non-blank cells? I have a column of data that in some cases has from zero to
five blank cells between non-blank ones. If there are not two blank cells
between the non-blank ones, it is an indication of a potential coding error
within the data source. I'm looking for an easy way to do my data scrub.

Any ideas would be helpful.

Thanks.

=COUNTBLANK(A5:A11)
or
=1-ROW(A5)+ROW(A11)-COUNTA(A5:A11)

de3pending upon whether you want to count blanks or empties
--
Gary''s Student - gsnu200814


"AndyH" wrote:

Does anyone know a simple way to count the number of blank cells between
non-blank cells? I have a column of data that in some cases has from zero to
five blank cells between non-blank ones. If there are not two blank cells
between the non-blank ones, it is an indication of a potential coding error
within the data source. I'm looking for an easy way to do my data scrub.

Any ideas would be helpful.

Thanks.

Andy,

Try

=IF(SUMPRODUCT((A2:A101="")*(A3:A102<"")*(A1:A100 <""))0,"Coding error","All OK")

Note the slight offsets of the ranges...

HTH,
Bernie
MS Excel MVP


"AndyH" wrote in message
...
Does anyone know a simple way to count the number of blank cells between
non-blank cells? I have a column of data that in some cases has from zero to
five blank cells between non-blank ones. If there are not two blank cells
between the non-blank ones, it is an indication of a potential coding error
within the data source. I'm looking for an easy way to do my data scrub.

Any ideas would be helpful.

Thanks.

Hi,

If you just want to be alerted to the fact that two consecutive cells are
blank why not try conditional formatting:

1. Select all the data in a column, suppose its A2:A1000, and choose Format,
Condtional Formatting, from the first drop down pick Formula is and in the
second box enter the formula

=OR(AND(A2="",A1=""),AND(A2="",A3=""))

Click the Format button and choose a color on the patterns tab

If this helps, please click the Yes button

Cheers,
Shane Devenshire
"AndyH" wrote:

Does anyone know a simple way to count the number of blank cells between
non-blank cells? I have a column of data that in some cases has from zero to
five blank cells between non-blank ones. If there are not two blank cells
between the non-blank ones, it is an indication of a potential coding error
within the data source. I'm looking for an easy way to do my data scrub.

Any ideas would be helpful.

Thanks.