You can use the following basic syntax to exclude a cell from a range when using a formula in Google Sheets:

=SUM(FILTER(B2:B11,B2:B11<>B5))

This particular formula will calculate the sum of the values in the range **B2:B11** and exclude cell **B5** from the calculation.

The **<>** symbol in Google Sheets stands for “not equal.” In this formula we first filter the range to include all cells not equal to B5, then we calculate the sum of the remaining cells.

To perform a different calculation, simply replace SUM with another function such as AVERAGE or COUNT.

The following example shows how to use this syntax in practice.

**Example: Exclude Cell from Range in Google Sheets**

Suppose we have the following dataset that contains information about various basketball players:

We can use the following formula to calculate the sum of values in the **Points** column, excluding the value for the player named Derrick:

=SUM(FILTER(B2:B11,B2:B11<>B5))

The following screenshot shows how to use this formula in practice:

From the output we can see that the sum of the values in the **Points** column excluding Derrick is **218**.

We can confirm this is correct by manually calculating the sum of each value in the **Points** column excluding Derrick:

Sum of Points (Excluding Derrick) = 22 + 28 + 14 + 37 + 35 + 28 + 22 + 14 + 18 = 218.

To exclude multiple cells from a range, simply use multiple **<>** symbols in the formula.

For example, we can use the following formula to calculate the sum of the values in the **Points** column excluding both Derrick and Frank:

=SUM(FILTER(B2:B11,B2:B11<>B5,B2:B11<>B7))

The following screenshot shows how to use this formula in practice:

The sum of the values in the **Points** column excluding both Derrick and Frank is **183**.

We can also replace SUM with a different function.

For example, we can use the following formula to calculate the average of the values in the **Points** column excluding both Derrick and Frank:

=AVERAGE(FILTER(B2:B11,B2:B11<>B5,B2:B11<>B7))

The following screenshot shows how to use this formula in practice:

The average of the values in the **Points** column excluding both Derrick and Frank is **22.875**.

**Additional Resources**

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

Google Sheets: How to Filter a Column by Multiple Values

Google Sheets: Filter for Cells that Do Not Contain Text

Google Sheets: How to Filter from Another Sheet

This implementation does not work, if you replaced your example with excluding Bert instead of Derrick, you’d realise that instead of ignoring the B5 cell, you’re ignoring B5 and any cells that happen to have the same value, in this case George. You should instead do something like:

SUM(FILTER(B2:B11, ROW(B2:B11)ROW(B5))

This ignores the value on row 5, only excluding the desired value no matter what it equals.

Thanks for sharing. Seemed like a neat solution but there is a shortcoming with this approach. The operator is acting on the contents of the cell rather than the reference to the cell. If someone else had the same score as Derrick or Frank their points would also be excluded.