Table of Contents
SUMIF Contains in Google Sheets is a function that allows users to add up the values in a range of cells that match a certain criterion. For example, if you want to find the sum of all values that contain the word “Apple”, you could use the SUMIF Contains function to do this. You would enter the range of cells you want to search, followed by the criteria, which in this case would be “Apple”. The function would then return the sum of all cells in the range that contain the word “Apple”.
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)
=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
The following tutorials explain how to perform other common operations in Google Sheets: