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])…

Sidebar