Table of Contents
To calculate the sum of cells that are not equal to a specific value, you can use the SUMIF() function which takes three arguments: the range of cells to be evaluated, the criteria to be used to determine which cells to include in the sum, and the range of cells to sum. For example, if you wanted to sum all of the cells in a column that were not equal to ‘X’, you would enter “=SUMIF(A:A,”<>X”, A:A)” into the formula bar. This would return the sum of all cells in the column that did not equal ‘X’.
You can use the following formulas to calculate the sum of values in Excel for cells that are not equal to some value:
Method 1: Calculate Sum If Cells Not Equal to Value
=SUMIF(A1:A100, "<>value", B1:B100)
This formula calculates the sum of values in B1:B100 where the value in A1:A100 is not equal to value.
Method 2: Calculate Sum If Cells Not Equal to Several Values
=SUMIFS(B1:B100, A1:A100, "<>val1", A1:A100, "<>val2", A1:A100, "<>val3")
This formula calculates the sum of values in B1:B100 where the value in A1:A100 is not equal to val1 or val2 or val3.
The following examples show how to use each method in practice.
Example 1: Calculate Sum If Cells Not Equal to Value
Suppose we have the following data in Excel:
We can use the following formula to calculate the sum of values in the Points column where the Team column is not equal to A:
=SUMIF(A1:A100, "<>value", B1:B100)
The following screenshot shows how to use this formula in practice:
The sum of the cells in the Points column where the cell in the Team column is not equal to “A” is 132.
We can verify this by manually calculating the sum of the Points column for all cells where the Team column is not equal to “A”:
Example 2: Calculate Sum If Cells Not Equal to Several Values
Once again suppose we have the following data in Excel:
We can use the following formula to calculate the sum of values in the Points column where the Team column is not equal to A or B:
=SUMIFS(B2:B13, A2:A13, "<>A", A2:A13, "<>B")
The following screenshot shows how to use this formula in practice:
The sum of the cells in the Points column where the cell in the Team column is not equal to “A” or “B” is 54.
We can verify this by manually calculating the sum of the Points column for all cells where the Team column is not equal to “A” or “B”:
Sum of Points: 12 + 20 + 9 + 13 = 54.