# SUM Ignore N/A in Excel( 7 Easiest Ways) In a large dataset, there is a possibility of having some null or blank cells. 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 #N/A in Excel.

To make it more explainable, I’m going to use a dataset of sales information of individual salesperson of different products. There are 4 columns in the dataset which are Sales Person, Laptop, Iphone and, Ipad. Here these columns represent the sales information of a particular product. ## 7 Ways to SUM Ignore N/A

### 1. Using SUMIF

You can use the SUMIF function to ignore #N/A errors.

To use the SUMIF function first, select the cell where you want to place your resultant value.
➤ Here, I’ve selected the cell F4
Then, type the following formula in the selected cell or into the Formula Bar.

`=SUMIF(C4:E4,"<>#N/A")` Here, selected the cells C4:E4 as range and proved not equal (<>)#N/A as criteria. So, the function will return the sum of the numeric values only.

Finally, press the ENTER key.
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

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

For that first, select the cell where you want to place your resultant value.
➤ Here, I selected the cell F4
Then, type the following formula in the selected cell or into the Formula Bar.

`=SUMIF(C4:E4,">0")` Here, the selected range is C4:E4 same as before but I changed the criteria. 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. Using SUM & IFERROR

Here you can use the SUM function and the IFERROR function to ignore #N/A errors.
The SUM function will calculate the sum and IFERROR will ignore the#N/Aerrors (it will ignore any error though).

Firstly, select the cell to place your resultant value.
➤ Here, I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

`=SUM(IFERROR(C4:E4,0))` Here in the IFERROR function selected cell range C4:E4 as value and given 0 in value_if_error. Now it will pass all the selected values except errors (which it will convert into 0) to the SUM function to calculate the sum.

In the end, press the ENTER key.
Then, it will show the Total Sales of the salesperson Ahmed. If you want, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column. #### An Alternate Way

You can use the same formula just by changing the value_if_error.

Here, I used “” as value_if_error. It will give the exact same result as before because this double-quote skips #N/A errors.

Type the following formula in the selected cell or into the Formula Bar.

`=SUM(IFERROR(C10:E10,""))` ### 3. Using SUM & IFNA

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

To begin with, select the cell to place your resultant value.
➤ Here, I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

`=SUM(IFNA(C4:E4,""))` Here in the IFNA function selected cell range C4:E4 as value and given (” “) in value_if_na. 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.

Now, press the ENTER key.
As a result, it will show the Total Sales of the salesperson Ahmed. Hence, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column. ### 4. Using SUM, IF & ISERROR

You can use the SUM function, IF function, and the ISERROR function altogether to ignore #N/A errors.

To use these functions together, select the cell to place your result.
➤ Here, I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

`=SUM(IF(ISERROR(C4:E4),0,C4:E4))` Here, selected the cell range C4:E4 as the value of the ISERROR function 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.

Finally, press the ENTER key.
Therefore, it will show the Total Sales of the salesperson Ahmed. In short, by using the Fill Handle you can AutoFill formula for the rest of the cells of the Total Sales column. ### 5. Using SUM, IF & ISNA

You can use the SUM function, IF function and the ISNA function altogether to ignore #N/A errors.

At this time to use these functions together, select the cell to place your result.
➤ Here, I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

`=SUM(IF(ISNA(C4:E4),0,C4:E4))` Here, in the ISNA function selected the cell range C4:E4 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.

Press the ENTER key it will show Total Sales of Ahmed. By using the Fill Handle, you can AutoFill formula for the rest of the cells of the Total Sales column. ### 6. Using AGGREGATE

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

First, select the cell to place your resultant value.
➤ Here, I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

`=AGGREGATE(9,6,C4:E4)` 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 C4:E4 as an array. Now, it will return the sum ignoring #N/A errors.

Finally, press the ENTER key.
Now, it will show the Total Sales of Ahmed. Consequently, you can use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column. ### 7. Using IFERROR

You also can use the IFERROR function to do the sum while ignoring #N/A errors.

First, select the cell to place your resultant value.
➤ Here, I selected the F4 cell.
Then, type the following formula in the selected cell or into the Formula Bar.

`=IFERROR(C4, 0) + IFERROR(D4,0)+ IFERROR(E4,0)` Here in the IFERROR  function selected the cell C4 as value and 0 as value_if_error. Added the rest of 2 cells using the same IFERROR function.
In C4 and E4 there is no error so the values of these two cells are derived where because of containing #N/A it gave 0 for D4.

Press the ENTER key now it will sum all the selected cells values while ignoring #N/A errors. Later, use the Fill Handle to AutoFill formula for the rest of the cells of the Total Sales column. ## Practice Section

I’ve given a practice sheet in the workbook to practice these explained ways to sum ignore #N/A. You can download it from the above. ## Conclusion

In this article, I tried to explain 7 methods of sum ignore #N/A 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.  