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. – Mike Fitzpatrick Aug 11 '10 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() … :-( – 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! – user221741 Mar 17 '16 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. – 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

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 :) – wilson Mar 18 '16 at 3:11

``````=SUBTOTAL(9,A1:A50)
``````
• 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)
```
• 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

This way it seems to work:

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

Not so robust but effective!