formula returning incorrect blanks

[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

 

[Dave O]

When CH14 is blank, ISNUMBER perceives it as a non-numeric so the IF is
returning the "false" value, a blank. I'm trying to think of
workarounds, and without knowing the rationale for the ISNUMBER the
best I can think of is to enter a zero instead of a blank.

 

[michelleelaine]

Hi,
Thanks but I cannot use zero's. 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.
Thanks,
Elaine

 

[Dave O]

Re-read your post: land the cell pointer on your formula and press F2
to edit, then convert it to an array formula by pressing
CTRL-SHIFT-ENTER. It will then behave as expected.

 

[Guest]

If you want zero sum to be blank,

=IF(SUM(CH12:CH14)>0,SUM(CH12:CH14),"")

 

[michelleelaine]

I have tried changing it to an array but then will get a blank cell
whenever any of the 3 cells are blank.

I must be using the wrong formula - what I want to do is have a formula
that will sum any cells within the range that have a number but if ALL
of the cells are empty I need the formula cell to remain blank.

 

[Dave Peterson]

You have other replies in .excel.

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

 

[michelleelaine]

in case anyone has a similar problem, I got this suggestion from Dave
Peterson and it works perfectly!

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

the >0 can be replaced with whatever number you want before a summation
is done (i.e. 1 value, 2 values, etc. present)