Here’s an overview of how you can ignore the N/A values or errors in a SUM function. The following picture shows all the formulas we’ll use.

**7 Simple Ways to SUM While Ignoring N/A in Excel**

Our sample dataset contains three columns (C through E) of items sold, but not all cells in the columns contain a value. We’ll sum these columns row-wise and display the result in column F.

**Method 1 – Use the SUMIF Function to SUM and Ignore N/A Errors**

__Steps:__

- Select the result cell, such as E5.
- Insert the following formula and press
**Enter**.

`=SUMIF(C5:E5,"<>#N/A")`

- Use the
**Fill Handle**to**AutoFill**formula for the rest of the cells in the column.

**Alternative for Method 1**

- You can also use the following formula:

`=SUMIF(C5:E5,">0")`

- Use the
**Fill Handle**to**AutoFill**formula.

**Read More:** Excel Sum If a Cell Contains Criteria

**Method 2 – Combine the SUM and IFERROR Function to SUM and Ignore N/A**

__Steps:__

- Apply the following formula in cell F5:

`=SUM(IFERROR(C5:E5,0))`

IFERROR will pass the value of a cell if it’s numeric or return 0 if it encounters a cell with an error, which will be used to sum the cells.

- Use the
**Fill Handle**to**AutoFill**formula for the rest of the cells in column**F**.

**Alternative for Method 2**

- You can also use the following formula:

`=SUM(IFERROR(C5:E5,""))`

**Read More:** Sum Formula Shortcuts in Excel

**Method 3 – Merge SUM and IFNA Functions to SUM and Ignore N/A**

__Steps:__

- Insert the following formula into
**F5**and hit**Enter**.

`=SUM(IFNA(C5:E5,""))`

IFNA specifically checks if the value of the cell is the #N/A error and returns the second argument. Otherwise, it returns the original value.

- 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

**Method 4 – Consolidate SUM, IF, and ISERROR Functions to SUM and Ignore N/A**

__Steps:__

- Insert the following formula into F5.

`=SUM(IF(ISERROR(C5:E5),0,C5:E5))`

- Hit
**Enter**and use the**Fill Handle**to**AutoFill**formula for the rest of the column.

**Read More:** How to Add Multiple Cells in Excel

**Method 5 – Mix SUM, IF, ISNA Functions to SUM and Ignore N/A**

__Steps:__

- Apply the following formula in
**F5.**

`=SUM(IF(ISNA(C5:E5),0,C5:E5))`

**AutoFill**the formula for the rest of the cells of column**F**.

**Method 6 – Apply the AGGREGATE Function to SUM and Ignore N/A**

__Steps:__

- Use the following formula in the selected cell (i.e.
**F5**) and hit**Enter**.

`=AGGREGATE(9,6,C5:E5)`

In **the AGGREGATE function,** **9 **as **function_num **applies the** SUM function, **while **6 **as **option **ignores error values.

**AutoFill**the rest of the cells in column**F**.

**Read More:** How to Sum Range of Cells in Row Using Excel VBA

**Method 7 – Use the IFERROR Function Multiple Times to SUM and Ignore N/A**

__Steps:__

- Use the following formula in F5 and press Enter.

`=IFERROR(C5, 0) + IFERROR(D5,0)+ IFERROR(E5,0)`

This formula manually adds values, so it might be cumbersome to write for larger datasets.

- Use the
**Fill Handle**to**AutoFill**the formula.

**Read More:** [Fixed!] Excel SUM Formula Is Not Working and Returns 0

**Download the Practice Workbook**

**Further Readings**

- How to Sum Multiple Rows and Columns in Excel
- Sum If a Cell Contains Text in Excel
- How to Sum If Cell Contains Specific Text in Excel
- Sum a Column in Excel
- How to Add Numbers in Excel
- Sum to End of a Column in Excel