You can use the following formulas to perform a SUMIFS function with a wildcard character in Excel:
#Sum values in B2:B10 where cells in A2:A10 contains string =SUMIFS(B2:B10, A2:A10, "*string*") #Sum values in B2:B10 where cells in A2:A10 start with string =SUMIFS(B2:B10, A2:A10, "string*") #Sum values in B2:B10 where cells in A2:A10 end with string =SUMIFS(B2:B10, A2:A10, "*string")
The following examples show how to use each formula in practice with the following dataset in Excel:
Example 1: SUMIFS with Cells that Contain String
We can use the following formula to sum the values in the Points column where the cell in the Team column contains “ets” in the name:
=SUMIFS(B2:B10, A2:A10, "*ets*")
The following screenshot shows how to use this formula:
The sum of points for the teams whose name contains “ets” is 50.
We can manually verify that this is correct by taking the sum of points for teams whose name contains “ets”:
- Teams with “ets” in name: Nets, Rockets, Hornets
- Sum of points: 17 + 21 + 12
- Sum of points: 50
Example 2: SUMIFS with Cells that Start with String
We can use the following formula to sum the values in the Points column where the cell in the Team column starts with “bu”:
=SUMIFS(B2:B10, A2:A10, "bu*")
The following screenshot shows how to use this formula:
The sum of points for the teams whose name starts with “bu” is 50.
We can manually verify that this is correct by taking the sum of points for teams whose name starts with “bu”:
- Team names that start with “bu”: Bucks, Bulls
- Sum of points: 15 + 34
- Sum of points: 49
Example 3: SUMIFS with Cells that End with String
We can use the following formula to sum the values in the Points column where the cell in the Team column ends with “avs” in the name:
=SUMIFS(B2:B10, A2:A10, "*avs")
The following screenshot shows how to use this formula:
The sum of points for the teams whose name ends with “avs” is 54.
We can manually verify that this is correct by taking the sum of points for teams whose name ends with “avs”:
- Team names that end with “avs”: Mavs, Cavs
- Sum of points: 24 + 30
- Sum of points: 54
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Replace #N/A Values in Excel
How to Ignore #N/A Values When Using Formulas in Excel