What did I do wrong THIS time?

  • Thread starter Thread starter Nick Xylas
  • Start date Start date
N

Nick Xylas

I am trying to add up the value of the numbers in a range of cells in
which some cells contain numbers and others contain text. In the sum
cell, I typed the formula "=SUMIF(A1:C9,ISNUMBER(A1:C9),A1:C9)". The
result should have been 434.5, but actually came out to zero. Can
anybody tell me what is wrong with my formula and how to correct it?
 
Nick said:
I am trying to add up the value of the numbers in a range of cells in
which some cells contain numbers and others contain text. In the sum
cell, I typed the formula "=SUMIF(A1:C9,ISNUMBER(A1:C9),A1:C9)". The
result should have been 434.5, but actually came out to zero. Can
anybody tell me what is wrong with my formula and how to correct it?
Click to expand...

I'm not sure you can use ISNUMBER with a range like that (I think it
only accepts single cells).
This array function should work
={SUM(IF(ISNUMBER(A1:C9),A1:C9,0))}
 
It looks like you'd want:
=sum(a1:c9)

This will ignore text--although not errors.

If you have errors, you could use:

=sum(if(isnumber(a1:c9),a1:c9))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
 
meatshield said:
I'm not sure you can use ISNUMBER with a range like that (I think it
only accepts single cells).
This array function should work
={SUM(IF(ISNUMBER(A1:C9),A1:C9,0))}
Click to expand...

I just realized how confusing that post was. I said you can't use
ISNUMBER like that, then I use it in same way. What I meant to say
was, I don't think you can use ISNUMBER like that unless you are using
an array function. (You enter array functions using Ctrl-Shift-Enter).
Also, a regular SUM function will add all of the values in a range and
ignore all of the non-number(non-error) values. So you might be able
to just use SUM(A1:C9)
 
Ron said:
The SUM function should ignore the text and just calculate on the numbers.

Does this work for you?
=SUM(A1:C9)

Does that help?
***********
Click to expand...
I got a #VALUE error when I did that, but I've just realised that's
because I have a #VALUE error in one of the cells. I thought it was
because of the text. Thanks for all your help, folks.
 
Back
Top