SUMIF cells if contains part of a text string in Excel and Google Sheets
To sum based on a condition you can use the SUMIF function, for i.e =SUMIF(A2:A4,"Germany",B2:B4) where A2:A4 = criteria_range; B2:B4 = sum_range.
=SUMIF(A2:A4,"3PIE",B2:B4) // criteria within formula
=SUMIF(A2:A4,C5,B2:B4) // criteria as a cell reference
A2:A4 = criteria_range; "3PIE" = criteria; B2:B4 = sum_range
Check below for a detailed explanation with pictures and how to use formulas in Excel and Google Sheets.SUMIF cells if contains part of a text string in Excel
How to SUMIF cells if contains part of a text string in Excel?
SUMIF CELLS IF CONTAINS PART OF TEXT STRING — EXCEL FORMULA AND EXAMPLE
=SUMIF(A2:A4,"*3PIE*",B2:B4) // criteria within formula
=SUMIF(A2:A4,"*"&C5&"*",B2:B4) // criteria as a cell reference
A2:A4 = criteria_range
"*3PIE*" = criteria
B2:B4 = sum_range
💡 Wildcard: The * character allows for any number (including zero) of other characters to take its place.
💡 In this example, it’s used to find all cells that include the text "3Pie". This search is not case-sensitive, so "3Pie" is considered the same as "3PIE" or "3pie"
SUMIF cells if contains part of a text string in Google Sheets
How to SUMIF cells if contains part of a text string in Google Sheets?
SUMIF CELLS IF CONTAINS PART OF TEXT STRING — GOOGLE SHEETS FORMULA AND EXAMPLE
=SUMIF(A2:A5,"*3PIE*",B2:B5) // criteria within formula
=SUMIF(A2:A5,"*"&C5&"*",B2:B5) // criteria as a cell reference
A2:A5 = criteria_range
"*3PIE*" = criteria
B2:B5 = sum_range
💡 Wildcard: The * character allows for any number (including zero) of other characters to take its place.
💡 In this example, it’s used to find all cells that include the text "3Pie". This search is not case-sensitive, so "3Pie" is considered the same as "3PIE" or "3pie"
- Sum by month
- Sum total sales based on quantity & price
- Combine date and time
- Convert 1-12 to month name
- Dynamic current date and time
- Count blank cell
- Count cell between two values
- Count cells that contain numbers and ignore blank
- Count cells that do not contain numbers and ignore blank
- Extract left before first space
- Extract right after first space
- Extract the nth words in a text string
- Extract domain URL from a link
- Flip the first and last name
- Transpose column to row and ignore blank
- Transpose row to column and ignore blank
- Sum by month
- Count cell between two values
- Combine two or more cell
- SUMIF formulas and example
- SUMIFS formulas and example
- COUNTIF formulas and example
- IF formulas and example
- TEXT formulas and example
- CONVERT formulas and example
- Change the negative number to zero
- Change negative to positive numbers
- Calculate percentage changes for containing zero or negative values
- Convert date to text
- Convert time to text
- Convert weekday string to number
- Combine two or more cells with a line break
- Combine two or more cells and transpose
- Count cells over 10 characters
- Count cells that are not blank
- Count if cells contain a specific text
- Count cells that begin with a specific text
- COUNTIF based on multiple criteria