To sum values when a cell contains a particular word, use SUMIF with asterisks around the word in its criteria. With descriptions in A2:A8 and amounts in B2:B8, enter =SUMIF(A2:A8,"*coffee*",B2:B8) to add the amounts for rows whose descriptions contain “coffee.”
What SUMIF does
SUMIF adds numbers that meet one condition. It checks each cell in a criteria range, then adds the corresponding cell from a sum range when the condition is met. This makes it useful for questions such as “How much did we spend on orders mentioning coffee?” or “What are sales for products containing the word lamp?”
The function’s arguments are the range to check, the condition to apply, and, optionally, the range of numbers to add. If you leave out the sum range, Excel adds qualifying cells in the criteria range itself. For a text search tied to amounts in another column, specify both ranges so Excel knows what to inspect and what to total.
The asterisk is a wildcard: it stands for any number of characters, including no characters. Putting one before and after a word lets other text appear on either side of it. The match can therefore occur at the start, middle, or end of a cell.
Build the basic formula
Arrange the descriptions and values in corresponding rows. In this example, column A holds item descriptions and column B holds their amounts. The criteria range and sum range should cover the same number of rows and line up row by row.
- First argument: the cells Excel searches, such as A2:A8.
- Second argument: the text condition, written as “*coffee*” to find coffee anywhere in a description.
- Third argument: the values to add when the corresponding description qualifies, such as B2:B8.
With descriptions in A2:A8 and amounts in B2:B8, enter =SUMIF(A2:A8,"*coffee*",B2:B8). Excel checks each description in column A and adds the amount from the matching row in column B. The formula can go in any empty cell outside the source data, such as D2.
Worked example: total matching rows
Suppose the rows contain these descriptions and amounts: coffee beans, 18; iced coffee, 6; tea, 12; Coffee mug, 9; decaf coffee, 14; coffee-table book, 22; and cocoa, 5. The capitalization differs in one description, and one result contains “coffee” as part of a hyphenated phrase.
With those descriptions in A2:A8 and amounts in B2:B8, use =SUMIF(A2:A8,"*coffee*",B2:B8). The matching amounts are 18, 6, 9, 14, and 22, so the returned total is 69. Tea and cocoa do not match. The “coffee-table book” row does match because SUMIF is looking for the character sequence coffee, not checking whether it is a standalone word.
This distinction matters when interpreting a result. If the task is to find any description containing those letters, the wildcard formula fits. If you mean only a complete word, check whether the data’s punctuation and wording could produce unwanted matches before relying on the total.
Use a cell for the search word
A criteria word does not have to be typed directly into the formula. Keeping it in a separate cell makes the report easier to reuse: change the word in that cell and the total updates without editing the formula. This is handy for a summary sheet with a list of categories or search terms.
With descriptions in A2:A8, amounts in B2:B8, and the word coffee entered in D2, use =SUMIF(A2:A8,"*"&D2&"*",B2:B8). The ampersands join the wildcard characters to the cell’s contents, making the effective criterion *coffee*. For the example data above, the result is 69. If D2 is empty, the criterion becomes two wildcards, which can match text broadly and produce a misleading total; consider leaving the result blank or showing a prompt until a search term is entered.
When typing the formula in Excel, use ordinary quotation marks around the asterisk strings. The HTML entity & in this article represents the ampersand operator in the formula.
What “contains” does—and does not—mean
SUMIF text matching is not case-sensitive, so coffee, Coffee, and COFFEE match the same criterion. The asterisk accepts any length of surrounding text, including spaces, punctuation, or numbers. It does not require a space or other boundary around the search term.
That means a search for “art” can match “artist,” “cart,” or “art supplies.” Similarly, “cat” can match “catalog.” If those are false positives, use a more specific phrase, clean or categorize the source data, or build a separate approach that tests word boundaries. SUMIF’s wildcard criterion alone is a substring test, not a natural-language word search.
The question mark wildcard represents exactly one character. For instance, a criterion such as “cof?ee” can match one character in the question-mark position, but it will not match a variable number of characters there. A tilde escapes a wildcard when you need to search for a literal asterisk or question mark in the source text: use ~* to mean a literal asterisk and ~? to mean a literal question mark.
Adding another condition
If a row must meet two or more conditions, SUMIFS is usually the more suitable function. It sums a selected range while checking multiple criteria ranges. For example, you might total descriptions containing coffee only for one region, or total matching items for a particular month. Each condition is applied to the same row, so all the conditions must be true for its amount to be included.
With descriptions in A2:A8, amounts in B2:B8, and region names in C2:C8, use =SUMIFS(B2:B8,A2:A8,"*coffee*",C2:C8,"North") to total coffee-containing descriptions in the North region. This example assumes the three ranges refer to the same seven records. The result depends on the region values in column C; only rows meeting both the text and region conditions are added. Note that SUMIFS puts the sum range first, unlike SUMIF.
SUMIFS does not replace the wildcard idea: the criteria “*coffee*” still means that the description contains those letters. It simply adds another filter. In Microsoft 365 for Windows, both functions are available; the same formula syntax works in Excel for Mac and Excel for the web, though ribbon layout and some interface details can differ.
Common errors and how to fix them
- No matches: Check that the spelling in the criterion is present in the source text, and confirm that the formula points to the intended description column. Wildcard searches do not fix a misspelled search term.
- Wrong totals: Confirm that the sum range corresponds row for row with the criteria range. If one range starts or ends on a different row, values may be associated with the wrong descriptions.
- Unexpected matches: Remember that the criterion searches for a substring. A short term may occur inside a longer word, or a punctuation variation may still contain the searched letters.
- Numbers stored as text: SUMIF can evaluate a criteria range containing text, but the values to add need to be usable as numbers. If amounts look numeric but do not add, check whether they are stored as text and convert them as needed.
- Copied formulas shift: If you copy the formula to other cells and need to keep the source ranges fixed, use absolute references. With descriptions in A2:A8 and amounts in B2:B8, a fixed-range version is
=SUMIF($A$2:$A$8,"*coffee*",$B$2:$B$8). For the example data, it still returns 69.
Also check for hidden spaces or inconsistent descriptions if a result seems surprising. A wildcard can tolerate extra characters around the search word, but it cannot make a different spelling equivalent. For larger reports, Excel tables can help keep formulas tied to labeled columns as records are added, rather than requiring you to update fixed row endpoints manually.
SUMIF versus other ways to total matches
SUMIF is a good choice when you need one contains-text condition and one amount range. It is shorter and easier to audit than a more elaborate formula for that task. Choose SUMIFS when you need additional conditions, such as a region, salesperson, or date range. Both functions can use wildcards in text criteria.
Filtering a table is useful when you want to inspect matching records, not just calculate their total. A filter makes the individual rows visible and can help reveal why a subtotal is unexpected. It is not a replacement for a formula when you need a total that updates as the data changes or a compact summary alongside other calculations.
If the same categories are used repeatedly, a dedicated category column is often more reliable than searching free-form descriptions. For example, recording “Coffee” as a consistent category avoids accidentally including a “coffee-table book.” Clean, structured data makes the criteria simpler and helps prevent ambiguous matches.
Frequently Asked Questions
Is SUMIF with asterisks case-sensitive?
No. SUMIF text criteria are not case-sensitive, so a search for coffee also matches Coffee or COFFEE.
How do I sum cells that contain a word in another cell?
Put the search word in a cell such as D2 and join it to wildcard characters in the criteria, as in =SUMIF(A2:A8,"*"&D2&"*",B2:B8). The descriptions go in A2:A8 and the amounts in B2:B8.
Does “contains” mean the whole word only?
No. The wildcard pattern finds the text anywhere inside a cell, even when it is part of a longer word. SUMIF does not enforce word boundaries with this criterion.
Can SUMIF match text at the end or beginning of a cell?
Yes. The pattern “*coffee*” allows characters both before and after the search word. Use “coffee*” for text that starts with coffee, or “*coffee” for text that ends with coffee.
Why does my SUMIF formula return zero?
Check the search spelling, the criteria range, and whether the descriptions actually contain the searched characters. Also verify that the amounts are numeric and that the sum range aligns with the criteria range.
Related
- Capitalize First Letter in Excel Quickly
- Superscript in Excel: Quick Key and Formatting Steps
- Quick Key to Zoom In Excel: Shortcuts and Zoom Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel UNIQUE Function Not Showing? Causes and FixesIf Excel does not recognize or display UNIQUE, first check your version: the function is available in Microsoft…
- Excel Cell Value From List: Find or Select a MatchTo return a value from a list when you know a matching item, use XLOOKUP; with product IDs…
- Excel Average Days Between Multiple Dates: Formulas and TipsWith dates in chronological order in A2:A6, calculate each gap in a helper column and average those gaps:…
- Conditional Formatting in Excel: If-Then Color RulesYes. In Excel, select the cells you want to format, then choose Home > Conditional Formatting and create…
- Insert Images in Excel With the IMAGE Formula Step by StepTo insert an image in an Excel cell with a formula, use the IMAGE function with a direct…
- Average Filtered Column in Excel: Formula and TipsTo average only the visible rows in a filtered Excel column, use =SUBTOTAL(1,B2:B100), replacing B2:B100 with your data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.