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

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

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

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

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

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

Use SUBTOTAL instead:

=SUBTOTAL(9,A1:A50)

1

This way it seems to work:

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

Not so robust but effective!

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

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