SUM, but ignoring #NUM!

  • Thread starter Thread starter Elijah
  • Start date Start date
E

Elijah

Hi, Does anyone know how I can SUM a range of values which also include the
#NUM! reference? I want to ignore these because the sum calculation also
returns #NUM!.

I also want to be able to sum a range of values using SUMIF and SUMPRODUCT.
Is this possible or do I need to make sure there are no #NUM! references in
my range?

Thanks

Elijah
 
Hi
better would be to change the other formulas so they don't return #NUM. You
may post these formulas.

Saying that a solution for your issue ciould be the following array formula
(entered with cTRL+SHIFT+ENTER):
=SUM(IF(ISERROR(A1:A100),,A1:A100))
 
It's better to avoid such errors than constructing expensive formulas t
cope with them:

SUM(...) ---> SUMIF(Reference,"<>#NUM")

SUMIF(), SUMPRODUCT() ---> {SUM(IF(ISERR(...),....))}
 
Another option based on Frank's suggestion:

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

Still ctrl-shift-entered.

(But a little less typing <bg>)
 
Thanks all,

I'm extracting items from a list using
=INDEX('sheet1'$A$1:$A$100,SMALL(IF('sheet1'!$C$1:$C$100<12,ROW('sheet1'!$C$1:$C$100)),ROW(1:1)))

and than a vlookup to extract values.

I want to accomodate for when additional items are picked up within
the extracted list and that's where the NUM! references occur.

Elijah
 
Back
Top