How to Calculate Sum of a Column Ignore #N/A in Excel

We often use Sum function to calculate the total amount of a column. If #N/A exists in the column, how can we ignore them and just calculate the numbers in the column. This article will show you the simple method to do count ignore the invalid #N/A.

First prepare a list of number contains #N/A in some cells. See the example below.

Sum a Column of Cells Ignore #N/A by Formula 1


In A11, enter the following  formula:

=SUMIF(A2:A10,”<9.99E+307″)

Ignore #NA in Excel 1

Click Enter on keyboard. Then the result is auto displayed in cell A11.

Ignore #NA in Excel 2

Sum a Column of Cells Ignore #N/A by Formula 2


In A11, enter another formula based on the SUMIF function:

=SUMIF(A2:A10,”<>#N/A”)

Ignore #NA in Excel 3

Click Enter. You can also get the result by this formula.

Ignore #NA in Excel 4

Related Functions


  • Excel SUMIF Function
    The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
Related Posts

How to Sum if Equal to Many Items or A Range in Excel

If we want to sum numbers from a range with criteria, we often select SUMIF of SUMIFS functions as the first choice to create a formula. The criteria can be a number or an array or a collection of some ...

How to Sum if Equal to X or Y in Excel

In daily work, if we want to sum numbers from a range, and only sum the numbers which being equal to X or Y in the range, we can create a formula with Excel build-in functions to get the result. ...

How to Sum Last N Days in Excel

In our daily life, we may want to sum amounts or sales for a specific period, for example in last N days. Sum numbers in Excel is easy to run, we can apply SUM function. But if we want to ...

How to Sum if Contains an Asterisk

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

How to Sum in Vertical Range

If we want to add numbers based on some conditions in Excel worksheet, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In this article, we will introduce you the method ...

Sidebar