How to exclude a cell from a range in Google Sheets

How to exclude a cell from a range in Google Sheets
Reading Time: 4 minutes

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.

Exclude cells from a range in Google Sheets in computing average
Exclude a cell from a range in Google Sheets

There are a bunch of tricks that can help us do that 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.

Sample data
Sample data to exclude a cell from a range in Google Sheets

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
Excluding a cell from a range in SUM function
Exclude a cell from a range in Google Sheets in the SUM function

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 F4.

Excluding a cell in the SUMIF function
Exclude a cell from a range in Google Sheets in the SUMIF function

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 a cell from a range in Google Sheets using the FILTER function

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 D4 and D6, respectively.

So we enter the following formula:

=AVERAGE(FILTER(D2:D7,D2:D7<>D3,D2:D7<>D5))
Excluding cells from the range using the FILTER function
Filtering multiple cells in a range in Google Sheets

We can likewise filter and exclude any of the cell values from the formula.

For example, the sum of the scores of those who got more than 80 in Maths 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.

Excluding multiple cells from a range
Filtering multiple cells in a range in Google Sheets in sum

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

Count cells by color in Google Sheets

Delete Empty rows in Google Sheets | 2 Min Quick Guide