Adding numbers with #VALUE! between

[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

 

[Domenic]

Hi Joey,

Try,

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

to be entered using CTRL+SHIFT+ENTER

Hope this helps!

 

[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

 

[Domenic]

Actually, this simpler formula seems to work as well,

=SUMIF(A1:A100,">0")

 

[Jason Morin]

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

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

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

Hi Domenic
and what would happen to zeros or negative numbers in this range

 

[Joey]

Frank & Domenic

Thanks guys, all the formulas work great.


Cheers
Joe

 

[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!

 

[Frank Kabel]

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!

Hi Domenic. Public holiday in Germany today
Frank