Quick Answer:A simple method to exclude a cell from a range in Google Sheets:
- Type in the SUM formula along with the range
- Subtract the cell number you want to exclude and press Enter
While working with data in Google Sheets, we may sometimes encounter situations where we need to exclude certain values in a cell or a range of cells from the calculation of the sum or average. Excluding a cell from a range in Google Sheets can be pretty handy especially in the calculation of average where blank cells can skew the result.
There are a bunch of tricks that can help us exclude a cell from a range in Google Sheets. In this blog, we’ll learn a few of them.
Tip:Use the minus (“-”) or not equal to (“<>”) operators to exclude a single cell from the range and the FILTER function to exclude multiple cells.
Let us start with a simple one.
You can make a copy of this spreadsheet for hands-on practice.
Exclude a single cell from a range in Google Sheets
Let us assume that the data given below are the marks scored by some of the crew members and Captain Ahab on the ship Pequod.
Suppose we want to find the sum of the total marks scored by the crew members, then need to exclude Ahab’s total score from the formula. We can do this in a couple of ways.
The first is to add all the values and subtract the value we want to exclude.
Enter the formula given below in an empty cell:
=SUM(F2:F7)-F3
We simply subtract the total of the score of Ahab, which is in F3 from the sum of the range and we get the sum of the total score of the crews as result.
This can also be done using the SUMIF function. The formula is:
=SUMIF(F2:F7,"<>"&F3)Note: The “<>” operator means “Not equal to”. This signals the formula to ignore the value in cell F3 to exclude the cell from the range.
Another way to achieve the same result is to filter Ahab’s score using the FILTER function.
We embed the FILTER function within the SUM function, as given in the formula below.
=SUM(FILTER(F2:F7,(F2:F7)<>(F3)))This formula basically means that we want to sum the values in the range F2:F7 while filtering the value in the cell F3 in the range.
Exclude more than one cell from a range in Google Sheets
Let us consider another example. This time, say, we want to know the average of the scores in Science of those who scored higher than 80. We then need to exclude the scores of Ahab and Queequeg, which are in cells D3 and D5, respectively.
For this we can use the AVERAGE function with the FILTER function. So we enter the following formula:
=AVERAGE(FILTER(D2:D7,D2:D7<>D3,D2:D7<>D5))
We can likewise filter and exclude any of the cell values from the formula.
For example, to find the sum of the scores of those who got more than 80 in Maths we can use the SUM function with FILTER. The formula will be as under.
=SUM(FILTER(B2:B7,B2:B7<>B3,B2:B7<>B4,B2:B7<>B5,B2:B7<>B7))This will return the sum of those who scored higher than 80 while filtering out the scores less than 80.
Conclusion
Excluding a cell from a range in Google Sheets or multiple cells can be quite useful if we want to ignore certain values from the range. And one can do that in Google Sheets using a number of ways as we have learned.
We hope this blog has been helpful to you. Visit our blog for more articles and tutorials on Google Sheets.
See also
How to count non-empty cells in Google Sheets