Table of Contents
The wildcard in the SUMIFS function in Excel allows you to find cells that contain either identical or similar data and then sum values from those cells. For instance, you can use wildcards to sum all cells containing “app*” in a column, which would include “apple”, “application”, and any other words that start with “app”. You can use an asterisk (*) to match any number of characters, or a question mark (?) to match any single character. It is important to note that the wildcards must be enclosed in double quotes within the formula.
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:
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