You can use the following basic syntax to calculate the mean, median, sum, standard deviation, etc. in Excel while ignoring #N/A values:

=AVERAGE(IFNA(A2:A21, "")) =MEDIAN(IFNA(A2:A21, "")) =SUM(IFNA(A2:A21, "")) =STDEV(IFNA(A2:A21, ""))

This syntax simply replaces #N/A values with blanks and then calculates the descriptive statistic you’re interested in.

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

**Example 1: Calculate Mean & Ignore #N/A Values**

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

The mean of the dataset (ignoring all #N/A values) is **14.76**.

**Example 2: ****Calculate Median & Ignore #N/A Values**

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

The median of the dataset (ignoring all #N/A values) is **14**.

**Example 3: ****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 **251**.

**Example 4: ****Calculate Standard Deviation & Ignore #N/A Values**

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

The standard deviation of the dataset (ignoring all #N/A values) is **6**.

**Additional Resources**

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

How to Replace #N/A Values in Excel

How to Easily Find Outliers in Excel

How to Find the Top 10% of Values in an Excel Column

The IFNA turns #NA values into blanks, but blanks (empty cells) into zeros. That might be ok for SUM, it is a problem for AVERAGE, MEDIAN, STDEV and things like VAR.S.

I solved it like this:

=AVERAGE(IF(ISNUMBER(A2:A21),A2:A21,””))