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!

Solution 1:

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

Solution 2:

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.

Solution 3:

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.