You can use the following formula in Excel to sum all values in a range where the corresponding value in another range is not equal to multiple values:
=SUMIFS(B2:B12,A2:A12,"<>Mavs",A2:A12,"<>Pacers")
This particular formula calculates the sum of the values in the range B2:B12 where the corresponding value in the range A2:A12 is not equal to Mavs or Pacers.
Note that if you have a long list of values you’d like to exclude from the sum, you may instead use the following syntax:
=SUM(B2:B12)-SUM(SUMIFS(B2:B12,A2:A12,{"Mavs","Pacers","Rockets","Spurs"}))
This particular formula calculates the sum of the values in the range B2:B12 where the corresponding value in the range A2:A12 is not equal to Mavs, Pacers, Rockets or Spurs.
The following example shows how to use this syntax in practice.
Example: Use SUMIFS Not Equal to Multiple Criteria in Excel
Suppose we have the following dataset in Excel that shows the number of points scored by basketball players on various teams:
We can use the following formula to calculate the sum of points scored by all players who are not on the Mavs or Pacers team:
=SUMIFS(B2:B12,A2:A12,"<>Mavs",A2:A12,"<>Pacers")
The following screenshot shows how to use this formula in practice:
We can see that the sum of points for players not on the Mavs or Pacers teams is 165.
We can verify this is correct by manually calculating the sum of points for each player not on either of these teams:
Sum of Points: 19 + 30 + 36 + 40 + 22 + 18 = 165
This matches the value that we calculated using the formula.
Now suppose that we would like to calculate the sum of points scored by all players who are not on the Mavs, Pacers, Rockets or Spurs:
=SUM(B2:B12)-SUM(SUMIFS(B2:B12,A2:A12,{"Mavs","Pacers","Rockets","Spurs"}))
The following screenshot shows how to use this formula in practice:
We can see that the sum of points for players not on the Mavs, Pacers, Rockets or Spurs teams is 77.
We can verify this is correct by manually calculating the sum of points for each player not on any of these teams:
Sum of Points: 19 + 40 + 18 = 77
This matches the value that we calculated using the formula.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Use SUMIF with Multiple Ranges
Excel: How to Use SUMIF with ISNUMBER
Excel: How to Use SUMIF with OR
