Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a range and need to sum it using SUM function but the result is N/A if there is any N/A value. How can I make the SUM function to treat the N/A value as 0 value? Please help!

share|improve this question
add comment

6 Answers

up vote 7 down vote accepted

Use Array Formula

=SUM(IF(ISNA(A1:A4),0,A1:A4))

Press CTRL+SHIFT+ENTER after entering the formula

Note: Replace A1:A4 with your range

share|improve this answer
1  
+1: A nice improvement on my solution - no need to modify the original data. –  Mike Fitzpatrick Aug 11 '10 at 6:57
add comment

The easiest way:

Use SUMIF the value > 0.

So the syntax for that one is

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

You will get the same result since it will ignore any non-numeric or 0 values.

share|improve this answer
2  
Your suggestion won't work with negative numbers. Thanks anyway. –  Nam G. VU Aug 23 '10 at 2:31
    
Could do =SUMIF(A1:B2, ">=0") + SUMIF(A1:B2, "<0") to include negative numbers. I suspect that would be faster than the accepted array formula solution, if performance is a concern. –  kizzx2 Dec 11 '12 at 1:46
add comment

Use SUBTOTAL instead:

=SUBTOTAL(9,A1:A50)
share|improve this answer
add comment

You can convert the #N/A to zeros in your source range and then use the normal SUM function:

=IF(ISNA(OriginalFormula), 0, OriginalFormula)
share|improve this answer
1  
Since office 2007 there's a new feature, IFERROR, and then it can look like this: =IFERROR(OriginalFormula,0) making it much nicer. –  agentmike Oct 15 '12 at 10:21
    
@agentmike, agreed, I use it frequently these days. –  Mike Fitzpatrick Oct 15 '12 at 21:58
add comment

If there are formulas I'd copy as numbers, replace #N/A in column with 0 and then sum as usually

share|improve this answer
add comment

This way it seems to work:

SUMIF(range;"<>#N/A";range)

Not so robust but effective!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.