# How to sum a range of cells with N/A values?

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!

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

• +1: A nice improvement on my solution - no need to modify the original data. Aug 11, 2010 at 6:57
• 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))` 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

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.

• Your suggestion won't work with negative numbers. Thanks anyway. Aug 23, 2010 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. Dec 11, 2012 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.

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

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

```=IF(ISNA(OriginalFormula), 0, OriginalFormula)
```
• Since office 2007 there's a new feature, `IFERROR`, and then it can look like this: `=IFERROR(OriginalFormula,0)` making it much nicer. Oct 15, 2012 at 10:21
• @agentmike, agreed, I use it frequently these days. Oct 15, 2012 at 21:58

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

``````=SUBTOTAL(9,A1:A50)
• I'm trying `sumtotal` but I have the same problem than with `sum` Feb 4, 2015 at 16:05
``````SUMIF(range;"<>#N/A";range)