17.6 C
London
Sunday, March 9, 2025
HomeSoftware TutorialsGoogle SheetsGoogle Sheets: How to Exclude Cell from Range in Formulas

Google Sheets: How to Exclude Cell from Range in Formulas

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

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:B11B5))

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:B11B5))

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

Google Sheets exclude cell from range

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:B11B5,B2:B11B7))

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

Google Sheets exclude multiple cells from range

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:B11B5,B2:B11B7))

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

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories