Summing a range with errors in it

[Helen]

If I have a range that has for example #VALUE! and numbers in a range is there any way of summing the range and for it not to error, but give the sum of the numbers?

 

[Keith1PHX]

Someone helped me with a similar problem I was having with #N/A.
You might try this:
=SUMIF(A1:A100,"<>#VALUE")

If it worked for #N/A, I would think it would work for #VALUE

Hope it helps

 

[Arun Sharma]

that formula works, but remember its #value! not just #value

 

[Keith1PHX]

Sorry, my bad

 

[Guest]

On 2002-03-02 09:29, Helen wrote:
If I have a range that has for example #VALUE! and numbers in a range is there any way of summing the range and for it not to error, but give the sum of the numbers?

If it's not beyond your control to have errors like #VALUE!, #DIV/0!, or #REF!, I'd suggest to hunt them down and eliminate their causes (excepting perhaps #N/A in the process, otherwise use for a range with different types of error the following array-formula:

=SUM(IF(NOT(ISERROR(A1:A6)),A1:A6))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Aladin