How to Sum Cells Matching Wildcard Criteria in Google Sheets Using SUMIF

Data analysis in Google Sheets frequently involves aggregating values based on partial text matches rather than exact identifiers. When working with inventory SKUs, customer transaction logs, bank statements, or categorised expense reports, target descriptions often contain dynamic prefixes, timestamps, or varied departmental suffixes (for example, INV-2026-0814, INV-2026-0922, or Software Subscription - Monthly). Calculating totals for an entire category using standard exact-match formulas requires continuous manual updates.

The SUMIF function in Google Sheets solves this challenge through native support for wildcard characters. By incorporating asterisks, question marks, and tildes into formula criteria, analysts can calculate sums based on begins-with, ends-with, contains, and fixed-character patterns dynamically. This guide demonstrates how to configure and use wildcard criteria within SUMIF in Google Sheets.

Understanding Supported Wildcard Characters

Google Sheets recognizes three distinct wildcard characters within evaluation criteria:

• Asterisk (*): Represents zero or more characters. Placing an asterisk before or after text enables partial match filtering (e.g. "*UK*" matches any string containing “UK”).
• Question Mark (?): Represents exactly one single character. Useful for matching structured codes with variable single digits or letters (e.g. "Part-???" matches “Part-101” but not “Part-12”).
• Tilde (~): The escape operator. If you need to search for an actual literal asterisk or question mark within text descriptions, precede the symbol with a tilde (e.g. "~*" or "~?").

Basic Syntax of the SUMIF Function

The structural syntax of the SUMIF function is defined as follows:

=SUMIF(range, criterion, [sum_range])

• range: The cell range evaluated against the condition (e.g. product names or invoice codes in A2:A100).
• criterion: The pattern or condition that determines which cells are included in the sum.
• sum_range (optional): The actual numerical cells to sum (e.g. revenue in B2:B100). If omitted, Google Sheets sums the cells inside range directly.

Common Wildcard Formula Patterns

The following examples demonstrate how to construct wildcard criteria for typical business scenarios:

1. Summing Cells Containing Specific Text Anywhere

To sum all transaction amounts in column B where the description in column A contains the word “Google” anywhere within the cell:

=SUMIF(A2:A100, "*Google*", B2:B100)

This formula successfully captures “Google Cloud”, “Invoice from Google LLC”, and “Annual Google Workspace”.

2. Summing Cells That Begin with a Specific Prefix

To aggregate revenue for all enterprise SKUs beginning with “ENT-“:

=SUMIF(A2:A100, "ENT-*", B2:B100)

3. Summing Cells That End with a Specific Suffix

To sum values for accounts ending with the department tag “-MKT”:

=SUMIF(A2:A100, "*-MKT", B2:B100)

4. Matching Fixed Character Counts with Question Marks

To sum records where a project tracking code begins with “PRJ” followed by exactly four characters:

=SUMIF(A2:A100, "PRJ-????", B2:B100)

This matches “PRJ-2026” or “PRJ-9041”, while ignoring shorter codes like “PRJ-10” or longer codes like “PRJ-2026-ALPHA”.

Combining Wildcards with Cell References Dynamically

Hardcoding text strings directly into formulas reduces flexibility. When building dynamic summary dashboards, reference an input cell (such as D2) and concatenate the wildcard symbols using the ampersand (&) operator:

Dynamic “Contains” Matching

If cell D2 contains the search keyword (e.g. Hardware), concatenate leading and trailing asterisks:

=SUMIF(A2:A100, "*" & D2 & "*", B2:B100)

Whenever the stakeholder enters a new term into cell D2, the formula recalculates the matching sum automatically without requiring syntax edits.

Dynamic “Begins With” Matching

=SUMIF(A2:A100, D2 & "*", B2:B100)

Handling Literal Asterisks and Question Marks

If your source data contains actual asterisks (such as footnote asterisks Product*) or question marks, searching with "*Product**" causes evaluation errors. Use the tilde escape operator:

=SUMIF(A2:A100, "*Product~**", B2:B100)

The ~* instructs the calculation engine to treat the second asterisk as a literal text character rather than a wildcard matcher.

Expanding to Multiple Wildcard Criteria with SUMIFS

When you need to match partial text across multiple distinct columns (for example, finding items containing “Apparel” in column A and ending in “UK” in column C), use the SUMIFS function. Note that SUMIFS places the sum_range first:

=SUMIFS(B2:B100, A2:A100, "*Apparel*", C2:C100, "*UK")

Troubleshooting Common Calculation Errors

• Case Sensitivity: SUMIF criteria in Google Sheets are case-insensitive. Searching for "*apple*" matches “Apple”, “APPLE”, and “aPpLe” uniformly.
• Spaces in Criteria: Placing accidental spaces inside wildcard strings (such as "* Google *") forces the engine to look for spaces surrounding the word, potentially excluding entries that begin or end at cell boundaries.
• Mismatched Range Dimensions: Ensure the range (e.g. A2:A100) and sum_range (e.g. B2:B100) span identical row counts to prevent formula evaluation mismatch errors.