Auto Sum not working

R

Robojohn

I need to show a total for a series of values.
Some of these values may be replaced with "N/A" as a result of an IF formula.
When this happens, the total does not always calculate, which has a knock-on
effect for other formulas.

Any ideas?
 
B

Bernard Liengme

Try this array formula
=SUM(IF(ISNA(A1:A8),0,A1:A8))
remember to complete it with SHIFT+CTRL+ENTER not just ENTER
best wishes
 
C

Chip Pearson

Neither AutoSum nor the SUM function ignores errors. If there is an error in
a cell, AutoSum will not reference cells above that cell. The SUM function
will return the same error as exists in any of its input cells. To SUM a
range and ignore errors, use the following array formula

=SUM(IF(ISNUMBER(A1:A10),A1:A10))

This will return the SUM of A1:A10, ignoring error values. Change both
instances of "A1:A10" to your range.

Since this is an array formula, you MUST press CTRL+SHIFT+ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces { }.

For more info about array formulas, see www.cpearson.com/excel/array.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
R

Rick Rothstein \(MVP - VB\)

To SUM a range and ignore errors, use the following array formula
=SUM(IF(ISNUMBER(A1:A10),A1:A10))

This will return the SUM of A1:A10, ignoring error values.
Click to expand...

Not entirely true... put =0/0 or =SQRT(-1) in one of the cells within the
range.

Rick
 
D

Dave Peterson

You sure?

It worked fine for me.

Rick Rothstein (MVP - VB) said:
Not entirely true... put =0/0 or =SQRT(-1) in one of the cells within the
range.

Rick
Click to expand...
 
B

Bernard Liengme

Did you try? Chip's formula works for me with DIV0, VALUE and NUM errors.
So does this more long-winded one =SUM(IF(ISERROR(A1:A10),0,A1:A10))
 
R

Rick Rothstein \(MVP - VB\)

Yes, I tried it before posting and it repeated the error for its result.
Just tried it again... it still doesn't work. Using Excel 2003 on Vista
Ultimate in case that makes a difference (although I don't see how it
would).

Rick
 
R

Rick Rothstein \(MVP - VB\)

Sigh! Never mind... I had forgotten to use Ctrl+Shift+Enter when entering
the formula.

What fooled me is that the formula worked fine using just a plain Enter key
when the range contained either numbers or text entries... however the plain
Enter key method of entering the formula did not survive the error
condition.

Sorry for the confusion.

Rick


Rick Rothstein (MVP - VB) said:
Yes, I tried it before posting and it repeated the error for its result.
Just tried it again... it still doesn't work. Using Excel 2003 on Vista
Ultimate in case that makes a difference (although I don't see how it
would).

Rick
Click to expand...
 
Top