Excel: How to Sum If Cells Contain Text


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.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Delete Rows with Specific Text
Excel: How to Check if Cell Contains Partial Text
Excel: How to Check if Cell Contains Text from List

Leave a Reply

Your email address will not be published.