Summing a range with errors in it

Helen · Mar 2nd, 2002, 10:29 AM

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 · Mar 2nd, 2002, 10:35 AM

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 · Mar 2nd, 2002, 11:11 AM

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

Keith1PHX · Mar 2nd, 2002, 11:17 AM

Sorry, my bad

Mar 2nd, 2002, 12:06 PM

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 2nd, 2002, 10:29 AM	#1
Helen Board Regular Join Date: Feb 2002 Posts: 103	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?

Mar 2nd, 2002, 10:35 AM	#2
Keith1PHX New Member Join Date: Feb 2002 Posts: 6	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

Mar 2nd, 2002, 11:11 AM	#3
Arun Sharma New Member Join Date: Mar 2002 Location: Southampton UK Posts: 4	that formula works, but remember its #value! not just #value

Mar 2nd, 2002, 11:17 AM	#4
Keith1PHX New Member Join Date: Feb 2002 Posts: 6	Sorry, my bad