10

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!

7 Answers 7

16

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

7
  • 1
    +1: A nice improvement on my solution - no need to modify the original data. Aug 11, 2010 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() … :-( May 27, 2014 at 14:48
  • @JoeCarroll, you can use =AVERAGE(IF(ISNA(A1:A4),"",A1:A4))
    – wilson
    May 28, 2014 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. May 29, 2014 at 11:20
  • Does not work with the magical Cr@pXcel 2013, but answer below (=aggregate) works like charm!
    – user221741
    Mar 17, 2016 at 12:29
5

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.

2
  • 2
    Your suggestion won't work with negative numbers. Thanks anyway.
    – Nam G VU
    Aug 23, 2010 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, 2012 at 1:46
4

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.

1
  • elegant solution for excel 2010 and onwards :)
    – wilson
    Mar 18, 2016 at 3:11
0

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

=IF(ISNA(OriginalFormula), 0, OriginalFormula)
2
  • 2
    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, 2012 at 10:21
  • @agentmike, agreed, I use it frequently these days. Oct 15, 2012 at 21:58
0

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

-1

Use SUBTOTAL instead:

=SUBTOTAL(9,A1:A50)
1
  • I'm trying sumtotal but I have the same problem than with sum
    – fguillen
    Feb 4, 2015 at 16:05
-1

This way it seems to work:

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

Not so robust but effective!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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