Excel: Use SUMIFS with Multiple Criteria in Same Column


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:

Excel SUMIFS with multiple criteria in same column

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

Leave a Reply

Your email address will not be published.