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