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.