Table of Contents
In Google Sheets, users can use the IFNA formula to ignore #N/A values when creating formulas. This formula checks if the value is #N/A and if so, it replaces it with an alternate value that the user can specify. This allows for accurate calculations even when some of the data is missing.
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.