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.
Read More: Excel Sum If a Cell Contains Criteria
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,""))
Read More: Sum Formula Shortcuts in Excel
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.
Read More: How to Add Specific Cells in Excel
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.
Read More: How to Add Multiple Cells in Excel
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.
Read More: How to Sum Range of Cells in Row Using Excel VBA
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.
Read More: [Fixed!] Excel SUM Formula Is Not Working and Returns 0
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
