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!

Nam G VU
  • 12,316

7 Answers7

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

wilson
  • 5,002
  • 2
  • 22
  • 39
  • 1
    +1: A nice improvement on my solution - no need to modify the original data. – Mike Fitzpatrick Aug 11 '10 at 06: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 04: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! –  Mar 17 '16 at 12:29
  • @Mark, agree that =aggregate(...) is a more elegant solution for excel 2010 and onwards. btw, i only have office 2010 / 2003 on hand, so i cannot test the array formula in 2013. – wilson Mar 18 '16 at 02:57
  • @wilson I wish I didn't have 2013 neither, but it's corp.policy :) It seems formulas work like interpreters (definition is incorrect on 'tech level', I know...) in 2013 so if you say something like "SUM A1:Ax IF A1:Ax [condition]..." excel stops processing on the 1st hit and ignores you want to SUM a range, not "SUM the first valid entry"(with itself?). Strange and ugly. –  Mar 18 '16 at 09:57
5

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.

fixer1234
  • 27,486
Muhammad Wajid Raza
  • 51
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.

JNK
  • 8,306
  • 28
  • 31
  • 2
    Your suggestion won't work with negative numbers. Thanks anyway. – Nam G VU Aug 23 '10 at 02: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 01:46
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)
Mike Fitzpatrick
  • 16,997
0

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

cynthia
  • 15
-1

Use SUBTOTAL instead:

=SUBTOTAL(9,A1:A50)
variant
  • 1,960
-1

This way it seems to work: 

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

Not so robust but effective!

Dave
  • 25,405
Bruno
  • 1