How to Ignore #N/A Values with Formulas in Google Sheets


You can use the following basic syntax to calculate different metrics in Google Sheets while ignoring #N/A values:

=AVERAGE(IFNA(A2:A14, "")) 

=SUM(IFNA(A2:A14, ""))

=IFNA(VLOOKUP(E2, A2:A14, 2, FALSE), "")

These formulas simply replace #N/A values with blanks and then calculates the metric you’re interested in.

The following examples show how to use this syntax in practice.

Example 1: Calculate Average & Ignore #N/A Values

The following screenshot shows how to calculate the average of a dataset that contains #N/A values:

The average value of the dataset (ignoring all #N/A values) is 9.7.

Example 2: Calculate Sum & Ignore #N/A Values

The following screenshot shows how to calculate the sum of a dataset that contains #N/A values:

The sum of the dataset (ignoring all #N/A values) is 97.

Example 3: Use VLOOKUP & Ignore #N/A Values

The following screenshot shows how to use the VLOOKUP function to return the value in the Points column that corresponds to the value in the Team column:

Notice that for any value in the Points column equal to #N/A, the VLOOKUP function simply returns a blank value instead of a #N/A value.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Remove Special Characters in Google Sheets
How to Use a Case Sensitive VLOOKUP in Google Sheets
How to Use ISERROR in Google Sheets

Leave a Reply

Your email address will not be published.