formula returning incorrect blanks

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

Well, this is REALLY weird. If I put this formula in a brand new spreadsheet

=IF(AND(ISNUMBER(A1:A3)),SUM(A1:A3),"")

It returns "".

After entering a number in A1 - no change
After entering another nunber in A2 - no change
After entering another nunber in A3 - I get the sum - as the formula would
suggest

Now if I select either A1 or A2 and Clear All, the sum remains but changes
to reflect the remaining values. This doesn't look right because of the AND()

Now I enter new values in A1 & A2 the sum updates

But, if I Clear All on A3 only, the sum disappears.

SO....THIS DOESN'T BEHAVE CONSISTENTLY, Right?




"michelleelaine@hotmail.com" wrote:

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

yep, you have it. I am so frustrated and have been working on this for
a week now. Thanks for trying!

Maybe that should be an array formula. It works perfectly then. Still don't
understand why it wirks as it does when not entered as an arry

Elaine -

The formula as written wants to sum those cells only if they ALL are
numbers, which is inconsistent with what your message says you want. If you
want the sum of ANY numbers, just use

=SUM(CH12:CH14)

If you really want a sum ONLY when all 3 cells have numbers, then the
formula must be entered as an array, ie., by pressing Ctrl-Shift-Enter


"Duke Carey" wrote:

> Well, this is REALLY weird. If I put this formula in a brand new spreadsheet
>
> =IF(AND(ISNUMBER(A1:A3)),SUM(A1:A3),"")
>
> It returns "".
>
> After entering a number in A1 - no change
> After entering another nunber in A2 - no change
> After entering another nunber in A3 - I get the sum - as the formula would
> suggest
>
> Now if I select either A1 or A2 and Clear All, the sum remains but changes
> to reflect the remaining values. This doesn't look right because of the AND()
>
> Now I enter new values in A1 & A2 the sum updates
>
> But, if I Clear All on A3 only, the sum disappears.
>
> SO....THIS DOESN'T BEHAVE CONSISTENTLY, Right?
>
>
>
>
> "michelleelaine@hotmail.com" wrote:
>
> > 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
> >
> >

Hi,

I had figured it needed to be an array as well but it still produces
the same result.

I unfortunately cannot use a simple sum equation either. 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

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 result to be blank.

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)