Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to SUM Ignore N/A in Excel (7 Simple Ways)

In a large dataset, there is a possibility of having some null or blank cells. The SUM function doesn’t work with #N/A values but there are several ways to do SUM ignoring #N/A values. In this article, I’m going to explain how to SUM Ignore NA in Excel.

In order to calculate the sum of each row ignoring #N/A, we can apply the SUM, SUMIF, IF, IFERROR, IFNA, ISNA, AGGREGATE, etc functions. I have explained them in the later part. Here is an overview of all 7 methods. Here, I have applied all the formulas for the same range (i.e. C5:E5) and thus, return the same result that I mentioned in the Result column.

Overview of SUM Ignore N/A


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


7 Simple Ways to SUM Ignore NA in Excel

We can calculate the summation ignoring #N/A in 7 simple ways.  Let’s dive into the details.


1. Use SUMIF Function to SUM Ignore N/A

You can use the SUMIF function to have summation and ignore #N/A errors. See the following section for more details.

Steps:

  • To use the SUMIF function first, select the cell where you want to place your resultant value. Here, I’ve selected the cell
  • Then, type the following formula in the selected cell or into the Formula Bar and press ENTER.
=SUMIF(C5:E5,"<>#N/A")
  • Here, I have selected the cells C5:E5 as range and proved not equal (<>)#N/A as criteria. So, the function will return the sum of the numeric values only. Now, it will show the Total Sales of the salesperson Ahmed.

Use SUMIF Function to SUM Ignore N/A

  • Later, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column.

AutoFill to SUM Ignore N/A


An Alternate Way of SUMIF Function

There is an alternate way to use the SUMIF function while ignoring #N/A errors.

Steps:

  • Select a cell and input the following formula in the selected cell to have SUM ignoring #N/A.
=SUMIF(C5:E5,">0")
  • Here, the selected range is C4:E4 same as before but I changed the As criteria, I have used a greater than (>) operator. If the selected values are greater than 0 then SUMIF will sum those values.
  • Press the ENTER key, eventually, it will show the Total Sales of Ahmed.

Alternative to Use SUMIF Function to SUM Ignore N/A

  • Now, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column.

AutoFill to Use SUMIF Function to SUM Ignore N/A

Read More: Excel Sum If a Cell Contains Criteria (5 Examples)


2. Combine SUM and IFERROR Function to SUM Ignore NA

Another simple yet effective way to sum keeping the #N/A ignored is to use a combination of SUM and IFERROR functions. The SUM function will calculate the sum and IFERROR will ignore the #N/A errors (it will ignore any error though). Just follow the following section for an explanation.

Steps:

  • Apply the following formula in your preferred cell (i.e. F5) to have sum ignore #N/A.
=SUM(IFERROR(C5:E5,0))
  • Here in the IFERROR function selected cell range C5:E5 as value and given 0 in Now it will pass all the selected values except errors (which it will convert into 0) to the SUM function to calculate the sum.

Combine SUM and IFERROR Function to SUM Ignore N/A

  • Use Fill Handle to AutoFill formula for the rest of the cells in column F.

AutoFill to Combine SUM and IFERROR Function to SUM Ignore N/A


An Alternate Way of SUM and IFERROR Functions

You can use the same formula just by changing the value_if_error. You can input “” as value_if_error. It will give the exact same result as before because this double-quote skips #N/A errors.

Steps:

  • Write the following formula in your preferred cell (i.e. F5) and press ENTER to have the sum ignore #N/A.
=SUM(IFERROR(C11:E11,""))

An Alternate Way of SUM and IFERROR Functions

Read More: Sum Formula Shortcuts in Excel (3 Quick Ways)


3. Merge SUM & IFNA Functions to SUM Ignore NA

We can also use the SUM function with the IFNA function to ignore #N/A errors. The SUM function will calculate the sum and IFNA will ignore the #N/A errors.

Steps:

  • Firstly, type the following formula in the selected cell or into the Formula Bar and press ENTER to have the desired output.
=SUM(IFNA(C5:E5,""))
  • Here in the IFNA function selected cell range C5:E5 as value and given ” “ in Now it will pass all the selected values except #N/A values (rather convert N/A into blank) to the SUM function to calculate the sum.

Merge SUM & IFNA Functions to SUM Ignore N/A

  • Hence, you can use the Fill Handle to AutoFill formula for the rest of the cells of column F.

AutoFilll to Merge SUM & IFNA Functions to SUM Ignore N/A

Read More: How to Add Specific Cells in Excel (5 Simple Ways)


Similar Readings


4. Consolidate SUM, IF and ISERROR Functions to SUM Ignore NA

You can consolidate the SUM, IF, and ISERROR functions altogether to ignore #N/A errors. Go through the following section for more details.

Steps:

  • First of all, select a cell and input the following formula in the selected cell to have SUM ignoring #N/A.
=SUM(IF(ISERROR(C5:E5),0,C5:E5))
  • Here, I have selected the cell range C5:E5 as the value of the ISERROR Now it will be the logical_test of IF. Then in the IF function provided 0 as value_if_true and the selected cell range as value_if_false. Now it will check the values and will return the zero for #N/A (or any error) and other non-zero values to the SUM function.

Consolidate SUM, IF and ISERROR Functions to SUM Ignore N/A

  • Lastly, use Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column.

AutoFill to SUM Ignore N/A

Read More: How to Add Multiple Cells in Excel (6 Methods)


5. Mix SUM, IF & ISNA Functions to SUM Ignore NA

A mixture of the SUM, IF, and ISNA functions altogether can be applied to ignore #N/A errors. Let’s see in detail at the below part.

Steps:

  • Apply the following formula in your preferred cell (i.e. F5) to have sum ignore #N/A.
=SUM(IF(ISNA(C5:E5),0,C5:E5))
  • Here, in the ISNA function selected the cell range C5:E5 as the value, it will work as logical_test to the IF function. Then in the IF function provided 0 as value_if_true and the selected cell range as value_if_false now it will check the values and will return the non-error values (#N/A) to the SUM function.

Mix SUM, IF & ISNA Functions to SUM Ignore N/A

  • By using Fill Handle, you can AutoFill formula for the rest of the cells of column F.

AutoFill to Mix SUM, IF & ISNA Functions to SUM Ignore N/A


6. Apply AGGREGATE Functions to SUM Ignore NA

You can use the AGGREGATE function to ignore #N/A errors while using sum.

Steps:

  • Firstly, type the following formula in the selected cell (i.e. F5) or into the Formula Bar and press
=AGGREGATE(9,6,C5:E5)
  • Here in the AGGREGATE function used 9 as function_num (9 means SUM) and 6 as options (6 means ignore error values) then selected the cell range C5:E5 as an Now, it will return the sum ignoring #N/A errors.

Return of the AGGREGATE Function

  • Consequently, use Fill Handle to AutoFill the rest of the cells of column F.

AutoFill the AGGREGATE Function

Read More: How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)


7. Use IFERROR Function to SUM Ignore NA

The IFERROR function is another effective way to do the sum while ignoring #N/A errors.

Steps:

  • Firstly, type the following formula in the selected cell or into the Formula Bar and press ENTER to have the desired output.
=IFERROR(C5, 0) + IFERROR(D5,0)+ IFERROR(E5,0)
  • Here in the IFERROR function selected to the cell C5 as value and 0 as Added the rest of 2 cells using the same IFERROR function. In C5 and E5, there is no error. So, the values of these two cells are derived where because of containing #N/A, it gave 0 for D5.

Return of the IFERROR Function

  • Later, use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column.

AutoFill the IFERROR Function

Read More: [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)


Conclusion

In this article, I tried to explain 7 methods of sum ignore NA in Excel. These different ways will help you to perform the sum with multiple #N/A values. Last but not least if you have any kind of suggestions, ideas, and feedback please feel free to comment down below.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo