Table of Contents
The SUMIFS function in Excel can be used to sum numeric values in a range of cells that meet criteria based on text strings. This is done by setting up criteria that match what is contained in the text cells, and then entering the range of cells to sum. The result is a sum of all the cells with the matching text string. This is a useful tool for summarizing data that contains text strings as well as numeric values.
You can use the following methods to sum cells in Excel that contain specific text:
Method 1: Sum Cells that Contain One Specific Text
=SUMIF(A2:A11, "*avs*", B2:B11)
This formula will sum the cells in the range B2:B11 where the corresponding cells in A2:A11 contain “avs” in the cell.
Method 2: Sum Cells that Contain One of Several Text
=SUMIF(A2:A11, "*avs*", B2:B11)+SUMIF(A2:A11, "*eat*", B2:B11)
This formula will sum the cells in the range B2:B11 where the corresponding cells in A2:A11 contain “avs” or “eat” in the cell.
The following examples show how to use each method in practice with the following dataset in Excel:
Example 1: Sum Cells that Contain One Specific Text
We can use the following formula to sum the values in the Points column where the Team name contains “avs” in the name:
=SUMIF(A2:A11, "*avs*", B2:B11)
The following screenshot shows how to use this formula in practice:
We can see that the cells where the Team column contains “avs” scored a total of 52 points.
We can manually verify this by summing the values where the Team column contains “avs”:
Sum of Points for Teams with “avs” in name: 12 + 5 + 8 + 10 + 17 = 52.
Example 2: Sum Cells that Contain One of Several Text
We can use the following formula to sum the values in the Points column where the Team name contains “avs” or “eat” in the name:
=SUMIF(A2:A11, "*avs*", B2:B11)+SUMIF(A2:A11, "*eat*", B2:B11)
The following screenshot shows how to use this formula in practice:
We can see that the cells where the Team column contains “avs” or “eat” scored a total of 65 points.
We can manually verify this by summing the values where the Team column contains “avs” or “eat”:
Sum of Points for Teams with “avs” or “eat” in name: 12 + 5 + 8 + 10 + 13 + 17 = 65.