Adding numbers with #VALUE! between

J

Joey

Hi all

I'm trying to add numeric data in a column but because it has "#VALUE!
inbetween I get a "#VALUE!" return.

Please let me know if there's a way of adding the numbers and ignorin
the "#VALUE!"

Many Thanks
Joe
 
D

Domenic

Hi Joey,

Try,

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

to be entered using CTRL+SHIFT+ENTER

Hope this helps!
 
F

Frank Kabel

Hi
one way: Try the following array formula (entered wirh
CTRL+SHIFT+ENTER):
=SUM(IF(ISERROR(A1:A100),,A1:A100))

But better: Try preventing these error results
 
J

Jason Morin

If the range were column A, the formula would look like:

=SUMIF(A:A,"<>#VALUE!")

HTH
Jason
Atlanta, GA
 
D

Domenic

Hi Frank,

Yep! I realized afterwards that it wouldn't be the appropriate formula.

BTW, nice to see you back in full force after a few days absence! :)
 
F

Frank Kabel

Domenic said:
Hi Frank,

Yep! I realized afterwards that it wouldn't be the appropriate
formula.

BTW, nice to see you back in full force after a few days absence!
Click to expand...
:)

Hi Domenic. Public holiday in Germany today :)
Frank
 
Top