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 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 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"