Using ISBLANK questions

[Candee]

Hello everyone.

I know I'm probably going about this the long way, but here goes:

I have a spreadsheet that has 35 columns of data. What I need to do i
count the number of times that there is data entered in specifi
non-adjacent(?) columns in each row (columns H, K, N, O, T, W, Z
AC).

Each row will vary, from all of those columns being empty to anywher
that they all contain data.

I had tried to set up the following formula (X 8 for each of th
columns): =IF(ISBLANK(H3),0,1)

This seems to work, but I am getting 1's where there should be 0's.
have tried selecting the entire worksheet, GoTo > Special > Blanks an
then clearing the contents of the selected cells thinking that ther
must be 's in the empty cells, but this didn't change the results
got.

So here's my question(s):Is there another way to clear all the empt
cells so that the formulas I have will work

OR

Is there a better way to accomplish what I am trying to do?

Any and all help and suggestions are very much appreciated, and thank
for taking the time to help.

Have a great day everyone
:cool

 

[Bernie Deitrick]

Candee,

Using Edit Go To... Blanks to try and clear cells won't work, since you
won't select the cells that you need to blank out.

You can see if the cell just looks blanks or is blank by comparing to "".

Try this array formula, entered with Ctrl-Shift-Enter, to count the number
of blanks:

=SUM((H3="")+(K3="")+(N3="")+(O3="")+(T3="")+(W3="")+(Z3="")+(AC3=""))

or this, to count the number of filled-in cells:

=8-SUM((H3="")+(K3="")+(N3="")+(O3="")+(T3="")+(W3="")+(Z3="")+(AC3=""))

HTH,
Bernie
MS Excel MVP

 

[Mike Lewis]

Hi, you might try:

=counta(h1,k1,n1,o1,t1,w1,z1,ac1)

Good luck

Mike