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.