excel replacement values for N/A

Question

I have a row having some values from A1 to A3. I have

1
3
#N/A 

I tried

=SUM(A1:A3)

But since I have value as #N/A am not able get the sum of 3 values. I want to treat #N/A as 0 and get the sum

Answer 1

If you are not aware of Aggregate function, you will need to use Array Formulas which can handle this kind of data easily.

=SUM(IFERROR(A1:A3,0))
=SUM(IF(ISNUMBER(A1:A3),A1:A3))

Both the formulas above will require a special key stroke Ctrl+Shift+Enter

Answer 2

The AGGREGATE function is great for ignoring error terms

=AGGREGATE(9,6,A1:A3)

---

Check the link I provided for more info but specific to the formula above:

• 9 represents the SUM formula
• 6 means ignore error terms (treat as equal to zero)