Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 11 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
1  
Nice. Never thought of using an array formula. I was happy with using =SUMIF(A1:A4, "<>#N/A") until I discovered it breaks if the spreadsheet is opened in a copy of Excel localised for another language! Unfortunately replacing "<>#N/A" with "ISNA()" doesn't work, but the array formula is a nice multilingual solution. Sadly, it's not a suitable approach for the same challenge with AVERAGEIF() … :-( – Joe Carroll May 27 '14 at 14:48
    
@JoeCarroll, you can use =AVERAGE(IF(ISNA(A1:A4),"",A1:A4)) – wilson May 28 '14 at 4:18
    
Thanks @wilson ! When I considered it briefly I didn't think of using "" instead of zero, silly me :-) Using zero affects the sample size, of course, which led me to dismiss the solution, but empty cells are simply ignored. – Joe Carroll May 29 '14 at 11:20
    
Does not work with the magical Cr@pXcel 2013, but answer below (=aggregate) works like charm! – Mark Mar 17 at 12:29

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
1  
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

There is a new function in Excel which will add all the values either positive or negative while ignoring NA's.

=aggregate(9,6, range of your data) 

9 is used for sum while 6 is for ignoring all NA's. There are other options, as well, beside addition. For example, you can do product, standard deviation, and so on.

More information on AGGREGATE here.

share|improve this answer
    
elegant solution for excel 2010 and onwards :) – wilson Mar 18 at 3:11

Use SUBTOTAL instead:

=SUBTOTAL(9,A1:A50)
share|improve this answer
    
I'm trying sumtotal but I have the same problem than with sum – fguillen Feb 4 '15 at 16:05

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. – Michael Oct 15 '12 at 10:21
    
@agentmike, agreed, I use it frequently these days. – Mike Fitzpatrick Oct 15 '12 at 21:58

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

This way it seems to work:

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

Not so robust but effective!

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .