You can use the following syntax to use a SUMIFS function with multiple criteria in the same column in Excel:
=SUM(SUMIFS(B2:B13,A2:A13,{"Guard","Center"}))
This particular formula calculates the sum of the values in the range B2:B13 where the corresponding value in the range A2:A13 is either “Guard” or “Center.”
The following example shows how to use this syntax in practice.
Example: Use SUMIFS with Multiple Criteria in Same Column
Suppose we have the following dataset in Excel that shows the number of points scored by various basketball players:
We can use the following formula to calculate the sum of points scored by all players who have a position of Guard or Center:
=SUM(SUMIFS(B2:B13,A2:A13,{"Guard","Center"}))
The following screenshot shows how to use this formula in practice:
We can see that the players who have a position of Guard or Center scored a total of 108 points.
We can manually verify this is correct by taking the sum of each of the Guards and Centers:
Sum of points for Guards and Centers: 12 + 8 + 8 + 9 + 20 + 22 + 29 = 108.
This matches the value that we calculated using the formula.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use SUMIF with Multiple Ranges
Excel: How to Use SUMIF with ISNUMBER
Excel: How to Use SUMIF with OR