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?

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Robojohn" <(E-Mail Removed)> wrote in message
news:6B720C1B-CD97-4630-90D5-(E-Mail Removed)...
>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?

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)


"Robojohn" <(E-Mail Removed)> wrote in message
news:6B720C1B-CD97-4630-90D5-(E-Mail Removed)...
>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?

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

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

Rick

You sure?

It worked fine for me.

"Rick Rothstein (MVP - VB)" wrote:
>
> > 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.
>
> Not entirely true... put =0/0 or =SQRT(-1) in one of the cells within the
> range.
>
> Rick

--

Dave Peterson

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))
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
>> 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.
>
> Not entirely true... put =0/0 or =SQRT(-1) in one of the cells within the
> range.
>
> Rick

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


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You sure?
>
> It worked fine for me.
>
> "Rick Rothstein (MVP - VB)" wrote:
>>
>> > 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.
>>
>> Not entirely true... put =0/0 or =SQRT(-1) in one of the cells within the
>> range.
>>
>> Rick
>
> --
>
> Dave Peterson

You must do something wrong, did you array enter it?


--
Regards,

Peo Sjoblom



"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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
>
>
>

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)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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
>
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> You sure?
>>
>> It worked fine for me.
>>
>> "Rick Rothstein (MVP - VB)" wrote:
>>>
>>> > 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.
>>>
>>> Not entirely true... put =0/0 or =SQRT(-1) in one of the cells within
>>> the
>>> range.
>>>
>>> Rick
>>
>> --
>>
>> Dave Peterson
>