You can use the following formula to calculate the sum in Excel only for cells that contain a specific partial text:
=SUMIF(A1:A13,"*text*",B1:B13)
This particular formula will calculate the sum of the values in the range B1:B13 only for the cells that contain “text” in the range A1:A13.
Note: The asterisks are wildcard characters that tell Excel to ignore any text before or after a specific string.
The following example shows how to use this formula in practice.
Example: Calculate Sum If Cell Contains Partial Text
Suppose we have the following dataset that contains information about the sales made at various retail stores:
We can use the following formula to calculate the sum of sales only for the stores that contain the partial text “Tier 1” somewhere in the store name:
=SUMIF(A2:A11,"*Tier 1*",B2:B11)
The following screenshot shows how to use this formula in practice:
The sum of sales made by stores that contain “Tier 1” in the name is 109.
We can manually verify this is correct by identifying each of the stores that have “Tier 1” in the name:
We can then calculate the sum of sales for these stores only:
Sum of Tier 1 Store Sales: 24 + 22 + 37 + 26 = 109.
This matches the value calculated by our formula.
Also note that this formula is case-insensitive.
For example, we could use “*TIER 1*” or “*tier 1*” or “*Tier 1*” in the formula and Excel will return the same result.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use SUMIF From Another Sheet
Excel: How to Use SUMIF with Multiple Ranges
Excel: How to Use SUBTOTAL with SUMIF
