Sum Partial Match? Meaning and How to Check
“Sum partial match” usually refers to a spreadsheet formula that adds values when a related cell meets a partial-text condition. In Excel and Google Sheets, the common approach is SUMIF with wildcard characters; the exact formula depends on which cells contain the text and which contain the numbers.
What “sum partial match” means
A partial match finds a piece of text inside a longer cell value instead of requiring the entire cell to equal the search term. For example, a criterion of *apple* can match “Green apple,” “apple juice,” or “Pineapple,” because the asterisks stand for any number of characters.
When you want to total numbers associated with those matching text cells, use a conditional-sum function. In Excel and Google Sheets, SUMIF handles one condition, while SUMIFS supports multiple conditions. The partial-match idea is supplied by the criterion’s wildcard, not by a separate function called “partial match.”
Basic SUMIF formula
The general syntax is:
=SUMIF(range, criteria, [sum_range])
- range is the cells checked for the text.
- criteria is the text or condition to look for.
- sum_range is the numeric cells to add. It is optional; if omitted, the function adds the cells in
range.
Suppose column A contains product descriptions and column B contains sales amounts. To total amounts for descriptions containing “apple,” enter:
=SUMIF(A2:A100,"*apple*",B2:B100)
The formula checks each cell in A2:A100, selects descriptions containing the text “apple,” and adds the corresponding values from B2:B100. The two ranges should cover corresponding rows and normally be the same size.
Wildcard characters and examples
The asterisk is the usual wildcard for a partial text match. Where supported in the spreadsheet application, a question mark matches one character, and a tilde can escape a wildcard when you want to search for it literally.
"*cat*"matches text with “cat” anywhere, such as “cat food” or “bobcat.”"cat*"matches text that begins with “cat,” such as “catalog,” but not “tomcat.”"*cat"matches text that ends with “cat,” such as “tomcat,” but not “cat food.”"A?C"matches three-character text beginning with A and ending with C, with any one character in between.
In standard Excel and Google Sheets use, text matching with these functions is generally not case-sensitive. Thus “Apple” and “apple” ordinarily meet the same criterion. If capitalization matters, a different approach is needed; SUMIF is not a case-sensitive text-matching function.
Using a cell for the search text
To make the search term easy to change, place it in a cell—for example, E1—and join it to wildcard characters with ampersands:
=SUMIF(A2:A100,"*"&E1&"*",B2:B100)
If E1 contains apple, the criterion becomes *apple*. This is useful for reports where someone needs to enter different keywords without editing the formula. If E1 itself contains an asterisk or question mark, that character may be interpreted as a wildcard rather than literal text, so the criterion may need to escape it.
Multiple conditions with SUMIFS
Use SUMIFS when the total must satisfy more than one condition. Its syntax places the sum range first:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
For example, to add amounts in B2:B100 where the description in A contains “apple” and the region in C is exactly “West,” use:
=SUMIFS(B2:B100,A2:A100,"*apple*",C2:C100,"West")
Each row must satisfy all the listed conditions to contribute to the sum. This differs from adding separate SUMIF formulas, which can count the same row more than once if the criteria overlap.
Common problems and checks
- Wrong range order: In SUMIF, the match range comes before the sum range. In SUMIFS, the sum range comes first.
- Unmatched row ranges: Check that the criteria and sum ranges cover the intended corresponding rows. A shifted range can return a plausible but incorrect total.
- Missing quotation marks: A wildcard criterion such as
*apple*should normally be written as a text criterion in quotation marks:"*apple*". - No matching results: Look for extra spaces, spelling differences, or a keyword that is not present in the descriptions. Also confirm that the values being added are numeric, not numbers stored as text.
- Unexpected matches: A broad criterion such as
"*art*"can match the letters within a longer word, not just the standalone word “art.” Use more specific descriptions or delimiters if that matters. - Separator differences: Depending on spreadsheet locale and settings, formulas may use semicolons instead of commas between arguments.
For a quick check, filter the description column for the same keyword and compare the visible rows with the rows you expect the formula to include. This helps distinguish a formula problem from a data-cleaning problem.
SUMIF versus other ways to match
SUMIF is usually the simplest choice for one partial-text condition. SUMIFS is convenient when you also need exact criteria such as a region, category, or date range. A filter can show matching records for inspection, but it is not itself a formula for a reusable total.
For more complex logic—such as case-sensitive matching, matching one of several words, or excluding certain phrases—consider a formula built with functions such as FILTER and SUM where available. These formulas can be more flexible, but syntax and availability vary by spreadsheet version. When a basic wildcard formula is enough, SUMIF is typically easier to read and maintain.
Frequently Asked Questions
How do I sum cells containing a word in Excel?
Use =SUMIF(text_range,"*word*",number_range), replacing the ranges and word with your own. The asterisks allow text before or after the word.
Does SUMIF match part of a cell?
Yes. Include wildcard characters in the criteria, such as "*east*", to match cells containing “east” anywhere in their text.
Can I use SUMIF for partial matches in Google Sheets?
Yes. Google Sheets supports the same common wildcard pattern in a SUMIF criterion, for example =SUMIF(A2:A100,"*apple*",B2:B100).
Is SUMIF case-sensitive?
No, ordinary SUMIF text criteria are not case-sensitive in Excel or Google Sheets. Uppercase and lowercase versions of the search text are generally treated alike.
How do I sum matches for two conditions?
Use SUMIFS, putting the cells to add first, followed by each criteria range and criterion. For a partial-text condition, use a wildcard criterion such as "*apple*".
Why does my partial-match sum return zero?
Check the spelling, wildcard placement, range alignment, and whether the sum cells contain real numeric values. Also verify that the matching text actually appears in the selected criteria range.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.