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”:
Sum of Points: 7 + 10 + 12 + 12 + 15 + 16 +20 + 18 + 9 + 13 = 132.
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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Sum Values by Category in Excel
How to Sum Across Multiple Sheets in Excel
How to Sum Filtered Rows in Excel