Counting blank cells between non-blank cells
 

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.

Counting blank cells between non-blank cells
 

=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.

Counting blank cells between non-blank cells
 

=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.

Counting blank cells between non-blank cells
 

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.

Counting blank cells between non-blank cells
 

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.