Table of Contents
The Excel SUMIFS Not Equal to Multiple Criteria formula is a powerful tool for summarizing data by specific criteria. With this formula, you can quickly analyze large datasets and produce accurate summaries of the data. It is especially useful for businesses wanting to make decisions based on their data. The SUMIFS Not Equal to Multiple Criteria formula allows you to quickly filter your data and produce summaries for various criteria, such as finding the total amount of sales for a particular region or product. This formula can help you to quickly identify trends in your data, enabling you to make more informed decisions.
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
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.