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.

**Table of Contents**hide

**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.