How to exclude cells from a formula in Excel

When working with data in Excel, it’s common to use formulas to perform calculations. However, there may be times when you need to exclude certain cells from a formula. This can be helpful when you have outliers or incorrect data that you don’t want to be included in your calculations. 

Here we have a dataset, in this dataset, there is a Data column containing random numbers which we need to Sum. In this tutorial, we’ll explore some ways to exclude cells from a formula in Excel but first, let’s take a look at the Dataset.

Method – 1 Not including the cells in the formula.

Step – 1 Write the formula.

  • Select the cell where you want to write the formula.
  • The syntax of the formula will be

=SUM(First_Range_of_cells, Second_Range_of_cells)

  • In our case we are excluding cells A4 and A5, so the formula will be

=SUM(A2:A3,A6:A11)

Step – 2 Finding the value.

  • In this formula, cells A4 and A5 are excluded from the calculation since they are not included in either of the specified ranges.
  • Press enter to apply the formula.

Method – 2 Using the SUMIF function with a criteria range.

Step – 1 Write the formula.

  • Select the cell where you want to write the formula.
  • The syntax of the formula will be

=SUMIF(First_Range_Of_Cells,”<> “&Cell_To_Exclude, Second_Range_Of_Cells)

  • In our case we are excluding cells A4, so the formula will be

=SUMIF(A2:A11,”<> “&A4,A2:A11)

Step – 2 Finding the value.

  • In this formula, cell A4 is excluded from the calculation, the criteria “<> “&A3 means “not equal to cell A3”.
  • Press enter to apply the formula.

Method – 3 Using minus () for excluding cells.

Step – 1 Apply the formula.

  • Select the cell where you want to display the result.
  • Type the formula:

=SUM(A2:A11)-A4

  • Syntax of the of the formula:

=SUM(Range_of_Cells)-Cell_to_Exclude

Step – 2 Finding the value.

  • In this formula, cell A4 is excluded.
  • Press enter to apply the formula.