You can use the following formulas in Google Sheets to calculate the sum of cells that contain certain strings:
Method 1: SUMIF Contains (One Criteria)
=SUMIF(A2:A11, "*string*", C2:C11)
This formula takes the sum of values in C2:C11 where the corresponding cell in the range A2:A11 contains “string.”
Method 2: SUMIF Contains (Multiple Criteria)
=SUMIFS(C2:C11, A2:A11, "*string1*", B2:B11, "*string2*")
This formula takes the sum of values in C2:C11 where the cell in the range A2:A11 contains “string1” and the cell in the range B2:B11 contains “string2.”
The following examples show how to use each method in practice.
Example 1: SUMIF Contains (One Criteria)
We can use the following formula to calculate the sum of values in the Points column for the rows where the Team column contains “Mav”:
=SUMIF(A2:A11,"*Mav*",C2:C11)
The following screenshot shows how to use this formula in practice:
From the output we can see that the sum of points for the rows where the team name contains “Mav” is 112.
We can also manually verify this is correct by taking the sum of points for the rows where the team name contains “Mav”:
Sum of Points: 21 + 14 + 19 + 30 + 28 = 112.
Example 2: SUMIF Contains (Multiple Criteria)
We can use the following formula to calculate the sum of values in the Points column for the rows where the Team column contains “Mav” and the Position column contains “Guar”:
=SUMIFS(C2:C11, A2:A11, "*Mav*", B2:B11, "*Gua*")
The following screenshot shows how to use this formula in practice:
From the output we can see that the sum of points for the rows where the team contains “Mav” and the position contains “Guar” is 35.
We can also manually verify this is correct by taking the sum of points for the rows where the team name contains “Mav” and the position contains “Guar”:
Sum of Points: 21 + 14 = 35.
Related: How to Use COUNTIF Contains in Google Sheets
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets:
How to Use SUMIF with OR in Google Sheets
How to Use SUMIFS with a Date Range in Google Sheets
How to Use SUMIF From Another Sheet in Google Sheets