formula returning incorrect blanks

M

michelleelaine

Problem:

When I enter the following equation into a cell with a blank in cell
CH12 or CH13 I get the correct answer (the sum of the cells with
numbers in them) but when there is a blank in cell CH14 I get an
incorrect answer (in the form of a clank cell).

=IF(AND(ISNUMBER(CH12:CH14)),SUM(CH12:CH14),"")

Does anyone have any suggestions?

Thanks,
Elaine
 
W

wjohnson

Try This: =SUM(CH12:CH14)
Your Formual is: =IF(AND(ISNUMBER(CH12:CH14)),SUM(CH12:CH14),"")
You will only "SUM" if the statement is TRUE - otherwise you get a NULL
or Blank Entry.
The ISNUMBER, forces the statement to "FALSE," when there is no entry
in CH14, which is the IF FALSE - "" part of your fromula. If you change
the "" to "Blank" you will get the word "BLANK" in the CELL when CH14
is empty.
 
M

michelleelaine

Hi,
Thanks but I cannot use a simple sum equation unfortunately. I have
stepwise calculations on raw data and I have to ensure that true zero's
are kept in while empty
cells (with no raw data) are continuously considered blank (not
zero's). For the example above I cannot use a simple sum function
(which would work if I was only missing 1 or 2 cells of the range)
because if all three cells are missing/blank in the previous
calculation, they have to return a blank, not a zero.

But your explanation regarding putting in a blank gives me an idea....

Thanks,
Elaine
 
D

Dave Peterson

Maybe you could count the number of cells with numbers in them:

=if(count(CH12:CH14)=3,sum(CH12:CH14),"")

I'm not sure how many have to be missing before you want to return "".
 
M

michelleelaine

I absolutely adore you! It worked! I put in

=if(count(ch12:ch14)>0,sum(ch12:ch14),"")

Thank you so much,
Elaine
 
Top