Sum if Cell Contains Text in Another Cell
This article explains how to sum values in Excel or Google Sheets when a related cell contains specific text, using functions like SUMIF, SUMIFS, and SUMPRODUCT. You’ll learn the exact formulas, how to handle partial matches and wildcards, and common pitfalls to avoid.
Why You Need This Formula
In spreadsheet work, you often have a table where one column holds categories or labels (like product names, departments, or status) and another column holds numbers you want to total. Instead of manually filtering and adding, you can use a conditional sum that only adds cells where the text column meets a condition. This is essential for reports, budgets, inventory, and any data analysis where you need totals based on text criteria.
The Core Function: SUMIF
The simplest way to sum if a cell contains text in another cell is the SUMIF function. Its syntax is:
SUMIF(range, criteria, [sum_range])
- range – the cells to check for the text (e.g., A2:A100).
- criteria – the text you’re looking for, in quotes (e.g., “Apples”) or a cell reference (e.g., D1).
- sum_range – the cells to add (e.g., B2:B100). If omitted, Excel sums the range itself.
Example: To sum all sales of “Apples” where column A has fruit names and column B has sales amounts:
=SUMIF(A2:A100, "Apples", B2:B100)
If the text is in another cell (say D1), you can reference it:
=SUMIF(A2:A100, D1, B2:B100)
Handling Partial Text Matches with Wildcards
Sometimes you need to sum cells that contain a substring, not an exact match. For example, you might want to sum all sales for products that include “Apple” (like “Apple Pie” or “Apple Juice”). Use wildcards:
- * (asterisk) – matches any sequence of characters.
- ? (question mark) – matches any single character.
To sum cells where column A contains “Apple” anywhere:
=SUMIF(A2:A100, "*Apple*", B2:B100)
If the text is in cell D1, you need to concatenate the wildcards:
=SUMIF(A2:A100, "*"&D1&"*", B2:B100)
This works in both Excel and Google Sheets.
Using SUMIFS for Multiple Conditions
When you have more than one text condition, use SUMIFS. The syntax is different – sum_range comes first:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Example: Sum sales where column A contains “Apple” AND column C equals “In Stock”:
=SUMIFS(B2:B100, A2:A100, "*Apple*", C2:C100, "In Stock")
You can use cell references and wildcards in each criteria.
SUMPRODUCT for Complex Logic (Case-Sensitive or OR)
If you need case-sensitive matching or OR logic, SUMPRODUCT is more flexible. For case-sensitive exact match:
=SUMPRODUCT((EXACT(A2:A100, D1)) * B2:B100)
For OR logic (sum if column A contains “Apple” OR “Banana”):
=SUMPRODUCT((ISNUMBER(SEARCH("Apple", A2:A100)) + ISNUMBER(SEARCH("Banana", A2:A100))) * B2:B100)
Note: SEARCH is case-insensitive; use FIND for case-sensitive. This works in Excel and Google Sheets, but in Excel you may need to enter it as an array formula (Ctrl+Shift+Enter) in older versions.
Common Mistakes and How to Avoid Them
- Mismatched ranges – Ensure the range and sum_range have the same number of rows. Otherwise, you get #VALUE! errors.
- Text vs. number criteria – If your criteria cell contains a number (like 123), SUMIF treats it as text. Use quotes or a cell reference with a number format.
- Wildcards in cell references – If you want to use a cell reference for partial match, remember to concatenate with asterisks as shown above.
- Leading/trailing spaces – Text with extra spaces won’t match. Use
TRIMon the data or criteria. - Case sensitivity – SUMIF and SUMIFS are case-insensitive. If you need case-sensitive, use SUMPRODUCT with EXACT or FIND.
Alternatives: PivotTables and FILTER
For large datasets, a PivotTable is often more efficient than formulas. You can drag the text column to Rows and the numeric column to Values to get sums for each category. In Excel 365 and Google Sheets, you can also use the FILTER function combined with SUM:
=SUM(FILTER(B2:B100, ISNUMBER(SEARCH(D1, A2:A100))))
This is more readable for complex conditions and dynamic arrays.
Frequently Asked Questions
Can I sum if cell contains text from a list?
Yes, use SUMPRODUCT with ISNUMBER(SEARCH) for each item in the list. For example, if the list is in D1:D3, use: =SUMPRODUCT((ISNUMBER(SEARCH(D1, A2:A100)) + ISNUMBER(SEARCH(D2, A2:A100)) + ISNUMBER(SEARCH(D3, A2:A100))) * B2:B100). This adds each condition.
Why is my SUMIF returning 0?
Check if the criteria text matches exactly (including spaces). If you’re using a cell reference, make sure the cell contains text, not a number. Also verify the sum_range is correct and doesn’t include empty cells that are formatted as text.
Does SUMIF work with dates?
Yes, but dates must be entered as text or using the DATE function. For example, =SUMIF(A2:A100, "1/1/2024", B2:B100) might not work if the date is stored as a serial number. Use =SUMIF(A2:A100, DATE(2024,1,1), B2:B100) or a cell reference with a real date.
Can I sum if the cell contains any text at all?
Yes, use the criteria “*” (asterisk) to match any non-empty text cell: =SUMIF(A2:A100, "*", B2:B100). This sums all rows where column A is not empty.
What’s the difference between SUMIF and SUMIFS?
SUMIF is for a single condition and takes range, criteria, sum_range. SUMIFS is for multiple conditions and takes sum_range first, then pairs of criteria_range and criteria. SUMIFS is available in Excel 2007 and later and Google Sheets.
How do I sum if cell contains text in another sheet?
Simply reference the other sheet in the range, e.g., =SUMIF(Sheet2!A:A, D1, Sheet2!B:B). Make sure the sheet name is spelled correctly and use single quotes if it has spaces, like 'My Sheet'!A:A.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.