Table of Contents
Google Sheets is a powerful spreadsheet tool that allows users to perform various calculations and data manipulations. In some cases, it may be necessary to exclude a specific cell from a range when using formulas. This can be achieved by using the “comma” operator to separate the cell references within the range. This will ensure that the excluded cell is not included in the calculation or manipulation performed by the formula. By following this method, users can easily exclude a specific cell from a range in Google Sheets formulas and obtain accurate results.
Google Sheets: Exclude Cell from Range in Formulas
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.
=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.
Cite this article
stats writer (2024). How can I exclude a specific cell from a range in Google Sheets formulas?”. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-exclude-a-specific-cell-from-a-range-in-google-sheets-formulas/
stats writer. "How can I exclude a specific cell from a range in Google Sheets formulas?”." PSYCHOLOGICAL SCALES, 25 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-exclude-a-specific-cell-from-a-range-in-google-sheets-formulas/.
stats writer. "How can I exclude a specific cell from a range in Google Sheets formulas?”." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-exclude-a-specific-cell-from-a-range-in-google-sheets-formulas/.
stats writer (2024) 'How can I exclude a specific cell from a range in Google Sheets formulas?”', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-exclude-a-specific-cell-from-a-range-in-google-sheets-formulas/.
[1] stats writer, "How can I exclude a specific cell from a range in Google Sheets formulas?”," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I exclude a specific cell from a range in Google Sheets formulas?”. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.