How to Ignore #NA Values With Excel Formulas

A lot of user experience issues and problems in Excel are related to formula issues. One of these issues is having the #N/A values in your range, which can be quite disturbing. These values mean that there is no value available.

We will show a couple of formulas that can help you to sum or to find the average of your range even if you have certain values that cannot be found.

Ignore #N/A Values with Excel Formulas

The first thing that we need to do is create a range with some values that cannot be found:

If we would go on and input the following formula:

=SUM(A2:A11)

To sum all the values, we would end up with the results shown in the picture below:

This is pretty much expected. We need to tweak this formula a little bit, and there are a couple of options. We will type them in a range C2:C4:

=SUMIF(A1:A11,"<>#N/A")
=SUM(IFNA(A2:A11, ""))
=SUM(IFERROR(A2:A11,""))

All of these formulas listed above will get us the same result:

Which is exactly the result that we want and would have expected.

If we want to find out the average of our range, we would resort to the similar formulas:

=AVERAGE(IFERROR(A2:A11,""))
=AVERAGE(IFNA(A2:A11, ""))

We will input these formulas in cells D2 and D3, and get the following results:

Personally, the best option to use is the IFNA formula, which takes into consideration that we will have #N/A values in our ranges.