To add numbers when a related cell contains a particular word, use SUMIF with asterisks around the word. For example, if descriptions are in A2:A100 and amounts are in B2:B100, =SUMIF(A2:A100,"*apple*",B2:B100) totals the amounts on rows where the description contains “apple.” The asterisks allow other text before or after the word. The steps below explain how to adapt the formula, use a word stored in a cell, and avoid common matching errors.
What You’ll Need
- Excel for Microsoft 365 on Windows. The same formula works in current Excel for Mac and Excel for the web.
- A column containing the text to check and a corresponding column containing numbers to add. Keep each text item and its amount on the same row.
- The word or phrase you want to find. You can type it directly into the formula or put it in a separate cell.
Step-by-Step
- Arrange the data in matching rows. Put the descriptions or categories in one column and the values to total in another. For example, enter descriptions in A2:A100 and amounts in B2:B100. Row 2 should contain the description and its amount, row 3 should contain the next pair, and so on. A header row is fine; just exclude it from the formula ranges.
- Choose the kind of match you need. Decide whether the cell must contain the word anywhere, or whether the entire cell must equal the word. If descriptions include extra details such as “green apple box,” a contains match is usually appropriate. If each cell contains only a single category such as “Apple,” an exact match is safer and avoids matching longer words that happen to include the same letters.
- Enter the contains-word formula. With descriptions in A2:A100 and amounts in B2:B100, enter this formula in an empty cell:
=SUMIF(A2:A100,"*apple*",B2:B100). Replace apple with the word you want to find. The first range is where Excel searches, the quoted criteria tells it what to find, and the final range contains the numbers to add. The two ranges must cover corresponding rows and have the same size. - Press Enter and check the total. Excel returns one number: the sum of B values on rows where the corresponding A cell contains the text apple. For example, if matching rows have amounts of 12, 8, and 15, the result is 35. Rows without the search text do not contribute. A blank or text value in the amount column is not added as a number.
- Use a cell for the search word if it may change. Type the word in D1. With descriptions in A2:A100 and amounts in B2:B100, enter
=SUMIF(A2:A100,"*"&D1&"*",B2:B100). The ampersands join the wildcard characters to the contents of D1. Change D1 to another word and the total updates without editing the formula. If D1 is blank, the criteria becomes two wildcards, which can match every text entry; enter a word before relying on the result. - Use an exact match when the whole cell must equal the criterion. With categories in A2:A100 and amounts in B2:B100, use
=SUMIF(A2:A100,"Apple",B2:B100). This matches cells whose full contents are Apple, not descriptions such as “Apple box.” For a criterion stored in D1, with the same data ranges, use=SUMIF(A2:A100,D1,B2:B100). Exact matching is useful for clean category lists, but it will not match cells with extra spaces or added words. - Decide whether substring matches are acceptable. The wildcard version looks for the character sequence anywhere, so “apple” can match “pineapple” as well as “apple pie.” If you mean a whole word within a longer description, check how words are separated in your data. For entries where words are separated by ordinary spaces and punctuation is absent, with descriptions in A2:A100, amounts in B2:B100, and the search word in D1, this formula checks for a space on each side:
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&D1&" "," "&A2:A100&" ")),B2:B100). It also catches the word at the start or end because the formula adds spaces around each description. It is not a general punctuation-aware word-boundary test; use a consistent data format before relying on it. - Fill the formula down only when you need separate totals. A single SUMIF already totals all matching rows. If you want a separate total for each search word, list the words in D1, D2, and so on. With descriptions in A2:A100, amounts in B2:B100, and the criterion in D1, enter
=SUMIF($A$2:$A$100,"*"&D1&"*",$B$2:$B$100)beside D1, then copy it down. The dollar signs keep the data ranges fixed while the criterion reference changes to D2, D3, and so forth.
In Excel for Windows, type the formula in the formula bar or directly into a selected cell and press Enter. Excel for Mac and Excel for the web use the same function and formula syntax. Depending on your regional settings, Excel may expect semicolons instead of commas between arguments; if Excel rejects a formula with commas, replace the argument separators with semicolons. Do not change the commas or other characters inside quoted text.
Common Mistakes
- Leaving out the wildcards. The criterion “apple” asks SUMIF to match the full cell contents. To find the text anywhere in a longer description, place an asterisk on both sides: “*apple*”.
- Assuming the wildcard finds a separate word. Asterisks match any number of characters, including none. They do not identify word boundaries, so apple also matches pineapple. Use an exact match for category-only cells, or standardize descriptions and use a suitable whole-word method.
- Reversing the ranges. The first SUMIF argument must be the cells Excel checks; the third argument must be the numbers to sum. If those are swapped, Excel may return an incorrect total or zero.
- Using ranges of different lengths. If the criteria range starts at row 2, the sum range should also start at row 2 and end on the corresponding last row. Mismatched ranges can make the results misleading.
- Including headers or unrelated rows. A text header in the sum range usually is not added, but inconsistent start and end rows can pair the wrong descriptions with amounts. Exclude headers and use aligned ranges.
- Expecting case-sensitive matching. SUMIF does not distinguish uppercase from lowercase. A criterion for apple will generally match Apple and APPLE as well.
Tips
- Use a cell reference for the search term if you expect to change it, or if several people will use the workbook. Label that cell clearly, such as “Word to find,” so the input is easy to identify.
- Test the formula on a small, known example first. Write down which rows should match and add their values by hand. Comparing that subtotal with Excel’s answer can reveal a range or matching issue quickly.
- Keep text and values in separate columns. A description such as “Apple 12” is harder to total reliably than a description in one column and the numeric value 12 in another.
- Trim accidental spaces in source data when exact matching is required. A cell containing “Apple ” has a trailing space and may not match a clean exact criterion. Editing the source or cleaning it in a helper column can prevent this issue.
- Use a consistent spelling for categories. SUMIF can find text, but it will not treat “apples,” “apple,” and a misspelling as the same word unless the criterion is designed to match those variations.
- If the workbook has many criteria and totals, consider converting the data to an Excel Table with Insert > Table. Tables can make formulas easier to read and can expand as you add rows, though ordinary cell ranges work perfectly well for a small list.
Troubleshooting
The answer is zero, but matching rows are visible. Confirm the search range points to the text column and the sum range points to the numeric column. Check whether the criterion has the wildcards needed for a contains match, and confirm there are no hidden differences such as an extra space or a different spelling. If the amounts are stored as text rather than numbers, convert them to numbers and recalculate.
The answer is larger than expected. Look for substring matches such as apple appearing inside pineapple, or for repeated occurrences across several rows. SUMIF adds the amount once for every matching row; it does not count how many times the word appears inside a single cell. Review the matching rows and decide whether exact matching or whole-word checking is more appropriate.
The formula appears in the cell instead of a total. Make sure the cell is not formatted as Text. Change its format to General, then select the formula, press F2, and press Enter to make Excel evaluate it. Also check that the formula begins with an equals sign and that Show Formulas is not enabled. On Windows, Ctrl+` toggles Show Formulas; on Mac, the shortcut can vary by keyboard layout, so use the Formulas tab’s Show Formulas command if needed.
Excel reports a formula error. Check that the quotation marks are straight double quotes, every parenthesis is paired, and each argument is separated correctly for your regional settings. If Excel expects semicolons, replace the commas that separate function arguments. Also confirm the ranges are valid and that the formula is entered outside the range it is summing.
The total does not update after changing the word. Confirm that the formula refers to the cell you edited. If calculation is set to Manual, select Formulas > Calculation Options > Automatic, or press F9 to recalculate. A workbook copied from another source may also contain fixed values instead of live formulas, so select the result cell and inspect the formula bar.
Frequently Asked Questions
Can SUMIF find a word anywhere in a cell?
Yes. Put an asterisk before and after the search text in the criteria, as in “*apple*”. With descriptions in A2:A100 and amounts in B2:B100, the formula is =SUMIF(A2:A100,"*apple*",B2:B100). This finds the text within longer entries, but it can also match the same letters inside a larger word.
Does SUMIF distinguish uppercase and lowercase?
No. SUMIF text matching is not case-sensitive, so a search for apple can match Apple or APPLE. If capitalization must affect the result, use a case-sensitive approach rather than relying on SUMIF alone.
Can I match a word stored in a cell?
Yes. With the search word in D1, descriptions in A2:A100, and amounts in B2:B100, use =SUMIF(A2:A100,"*"&D1&"*",B2:B100). The wildcards make it a contains match. Remove them if each cell must equal the contents of D1 exactly.
What if I need to match two different words?
For two separate contains-word totals, use one SUMIF for each word and add the results. Be aware that if a row contains both words, its amount can be counted twice. If the goal is to count each row only once when either word appears, use a different approach and verify the matching rows before using the total.
Will this work if my amounts are in another column?
Yes. Change the sum range to the column containing the values, keeping its start and end rows aligned with the criteria range. For example, if the descriptions remain in A2:A100 and the numbers are in C2:C100, use C2:C100 as SUMIF’s third argument instead of B2:B100.
Can I sum rows where the cell does not contain a word?
Yes. SUMIF supports a not-equal criterion with wildcards. With descriptions in A2:A100 and amounts in B2:B100, use =SUMIF(A2:A100,"<>*apple*",B2:B100) to add values where the description does not contain apple. Check how blank description cells should be treated in your data before using this for a final total.
Related
- Remove Strikethrough in Excel Numbers and Cells
- Excel Strikethrough Shortcut: How to Remove It Quickly
- Remove a Word in an Excel Cell With Find & Replace
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Sort an Excel PivotTable Largest to SmallestTo sort an Excel PivotTable from largest to smallest, right-click a number in the value column you want…
- Copy Formulas in Excel Without Creating External LinksTo copy a formula without linking it to another workbook, copy the formula text from the formula bar…
- How to Calculate Quality Percentage in Excel With FormulasTo calculate quality percentage in Excel, divide the number of checks that passed by the total number of…
- Excel Present Value Formula: PV Function and ExamplesIn Excel, use the PV function to calculate the present value of future cash flows: it discounts them…
- If You Block Someone on Outlook, Will They Know?No. When you block a sender in Outlook, Outlook does not send them a notice that you blocked…
- SUMIFS Excel Column Criteria: Examples and Rules ExplainedTo sum values in an Excel column only when another column meets a condition, use SUMIFS: =SUMIFS(D2:D8,A2:A8,"North"). Here,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.