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,

**Here these columns represent the sales information of a particular product.**

*Ipad.*## Download to Practice

**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/A**errors (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.