summation that does not consider cells with #value! error

  • Thread starter Thread starter daftpunk
  • Start date Start date
D

daftpunk

i am looking in the excel help but i don't know the name of the
function.

it's to calculate a sum but discriminating the #value! errors.

i've read about and tried the IF function but it fulfills another
purpose. do i need to create a sum that only accepts numbers? but how
do i tell the sum function to ignore errors (not try to add them up!)?

it's quite a simple problem, just ms help is rather vast!

thanks for any help:rolleyes:
 
One way:

=SUM(IF(ISERROR(B2:B100),0,B2:B100))

The formula is an array formula and must be entered with
<Shift><Ctrl><Enter>, also if edited later.
If done correctly, Excel will display the formula in the
formula bar enclosed in curly brackets { }. Don't enter
these brackets yourself.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
Hi

In addition to Leo's answer, the error trapping is used to elliminate #value
or #N/A etc. from range, you are trying to sum.
When you have in range you want to some formulas
=YourFormula
then you can replace them with new ones
=IF(ISERROR(YourFormula),"",YourFormula)
and there will be no #value! (or any other) errors in the range anymore, so
you can calculate sum of range without problems.


Arvi Laanemets
 
I assume you only want to skip #Value! errors, but flag other errors. Since
there is no direct test for #Value! errors only, perhaps this array formula
might work. "rng" is a range name.

=SUM(IF(ISERROR(rng),IF(ERROR.TYPE(rng)=3,0,rng),rng))

If I got it correctly, any other errors should pass thru.
 
Back
Top