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. ## 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. • Later, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column. #### 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. • Now, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column. ### 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. • Use Fill Handle to AutoFill formula for the rest of the cells in column F. #### 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,""))` ### 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. • Hence, you can use the Fill Handle to AutoFill formula for the rest of the cells of column F. ### 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. • Lastly, use Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column. ### 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. • By using Fill Handle, you can AutoFill formula for the rest of the cells of column F. ### 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. • Consequently, use Fill Handle to AutoFill the rest of the cells of column F. ### 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. • Later, use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column. ## 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.  