How to Sum If Cell Contains Text In Another Cell in Excel

Why This Task Matters in Excel

In virtually every data-driven role—from accounting and inventory management to marketing analytics—professionals store long descriptive strings in one column and related numeric values such as quantities, costs, or revenues in another. Real-world lists are rarely tidy; descriptions will often embed codes, locations, product names, or client identifiers inside longer text. When decision-makers ask for subtotals or performance figures “for everything that mentions X,” analysts must instantly convert those qualitative cues into quantitative insights.

Imagine a retail merchandise file where column B contains full item descriptions like “Blue Cotton Shirt – Summer 23 – SKU SHRT01” and column F holds sales revenue. The regional manager suddenly wants to see total sales for everything containing “Shirt.” Without a dynamic “sum-if-contains” technique, you might manually filter, copy, or write ad-hoc pivot tables each time a new keyword appears. That approach is error-prone, time-consuming, and collapses as soon as new descriptions arrive.

The ability to sum on a “contains” basis also empowers marketers to evaluate campaign tags buried in long UTM strings, finance teams to aggregate costs for project codes hidden inside general-ledger memo fields, and operations teams to total freight charges that mention specific ports in shipping notes. Across industries—manufacturing, healthcare, e-commerce, logistics—the pattern is identical: text clues identify the rows, while numbers deliver the totals.

Excel is perfectly suited for this job because its functions can combine text pattern matching with conditional aggregation in a single cell, remain linked to the live dataset, refresh automatically, and scale across thousands of rows without programming. Mastery of this technique lets you answer “What is the total value of any record that contains …?” instantaneously, connect the result to dashboards, and eliminate manual consolidation. Failing to learn it means slower reporting cycles, higher risk of mis-counting rows, and reduced confidence in your analytics. Moreover, the underlying skills—wildcards, named ranges, SUMIFS logic, dynamic arrays—extend directly to other tasks like text-to-columns preparation, advanced filters, and Power Query transformations. In short, “sum if cell contains text in another cell” is a keystone skill that unlocks faster ad hoc analysis and more robust automated reports.

Best Excel Approach

The most efficient and flexible way to sum numbers when a text cell contains the string held in another cell is the wildcard-enabled SUMIF (or SUMIFS for multiple conditions). SUMIF combines pattern matching and aggregation in one formula, updates instantly when the keyword cell changes, and is compatible with every Excel version released since 2007.

Syntax recap:

=SUMIF(range_with_text,"*"&criteria_cell&"*",range_to_sum)

• range_with_text – The column you want Excel to scan for the keyword
• criteria_cell – The cell that holds the keyword you care about
• \"\"&criteria_cell&\"\" – Surrounds the keyword with a leading and trailing asterisk, Excel’s wildcard for “any number of characters,” so descriptions merely containing the string qualify
• range_to_sum – The numeric column whose numbers you want totaled

Why this approach is best

• Requires no array gymnastics, volatile functions, or complex helpers
• Refreshes instantly when either the keyword or source data changes
• Preserves case-insensitivity, which matches most business usage
• Works equally in Excel for Windows, Mac, and Excel Online
• Simple enough to audit by non-technical teammates

When to use alternatives

• If you need case-sensitive matching, employ SUMPRODUCT + FIND.
• If you require multiple “contains” criteria simultaneously, layer SUMIFS with wildcards for each criterion.
• In dynamic-array enabled versions (365/2021), FILTER + SUM can be more performant on massive tables.

Parameters and Inputs

Before writing a formula, confirm these input requirements:

Text range ([A2:A1000])

• Data type: Plain text (strings). Can include spaces, punctuation, codes, or mixed case.
• Preparation: Trim obvious leading/trailing spaces, ensure there are no hidden non-breaking spaces that could mis-lead SEARCH operations.
• Validation: Optional Data Validation dropdown or a helper column to flag unexpected blanks.

Criteria cell ([K1])

• Data type: Single text string that represents the keyword or code to find.
• Input rule: Keep it short and unique enough to avoid unintended matches; for example, “Pro” will also match “Project” and “Proposal.”
• Edge cases: Empty criteria returns zero; wildcard characters (* or ?) typed directly will override your pattern.

Numeric range ([F2:F1000])

• Data type: Numeric (integers, decimals, currency).
• Preparation: Replace any “–” dash or text placeholders with 0, or exclude via formula to prevent #VALUE! errors.
• Currency formatting has no effect on computation.

Optional parameters

• Multiple criteria cells—use SUMIFS.
• Case sensitivity—use an array expression with EXACT or FIND.
• Dynamic named ranges—convert your data into a Table (Ctrl + T) and refer to structured names for automatic range expansion.

Edge-case handling

• Very large datasets (hundreds of thousands of rows) may require converting the sheet to an Excel Table plus FILTER helper to avoid full-column calculations.
• Duplicates are summed naturally, so if you need unique item totals, pivot tables or SUMPRODUCT + UNIQUE are necessary.

Step-by-Step Examples

Example 1: Basic Scenario

Scenario: You track event expenses in [Sheet1]. Column B lists cost descriptions and column C lists amounts. Cell E2 contains the keyword “Catering”. You need the total spent on any description that includes “Catering.”

Sample data
B\2 = “Venue Rental”
B\3 = “Catering Deposit”
B\4 = “Audio Equipment”
B\5 = “Final Catering Payment”
C\2 = 870
C\3 = 500
C\4 = 215
C\5 = 1 350
E\2 = Catering

Step-by-step

1. Click cell F2, label it “Catering Total.”
2. Enter the formula:

=SUMIF(B2:B5,"*"&E2&"*",C2:C5)

3. Press Enter. Result: 1 850.
   Behind the scenes, Excel evaluates each B-cell:

• B3 contains “Catering” → include 500
• B5 contains “Catering” → include 1 350
• Other descriptions do not match → ignore

4. Change E2 to “Audio.” The result instantly updates to 215.

Why it works
The asterisks on both sides of E2 tell Excel that any characters can appear before or after the keyword. SUMIF loops through B2:B5, creates a Boolean array of True/False values, multiplies by the amounts, and returns the sum.

Variations

• Put multiple keywords in a dropdown to allow quick re-analysis.
• Lock ranges with absolute references ([B:B]) and copy the formula across for different keywords.

Troubleshooting tips

• If you get zero but expect a value, check for trailing spaces in descriptions or in E2.
• If the result seems too high, your keyword may be too short (example: “Pro” matching “Project” and “Prototype”).

Example 2: Real-World Application

Scenario: A wholesale distributor stores 20 000 transactional lines in a Table called SalesData. The Description column contains product names plus color and pack size (e.g., “Organic Apple Juice 1 L – Case of 12”). Management needs sales totals for categories typed in a control sheet. They also want the report to isolate only those rows sold to customer segment “Retail,” maintained in column H.

Data setup

• SalesData[Description] – long text strings
• SalesData[Segment] – “Retail,” “FoodService,” “Export”
• SalesData[Revenue] – currency
• Control sheet cell B\2 = keyword (e.g., “Juice”)

Formula solution in Control sheet cell B4:

=SUMIFS(SalesData[Revenue],SalesData[Description],"*"&B2&"*",SalesData[Segment],"Retail")

Walkthrough

1. Convert raw data into an Excel Table (Ctrl + T). Table names expand automatically when new rows are appended.
2. Enter the formula above. SUMIFS accepts multiple criteria, so we supply two: description contains keyword, and segment equals “Retail.”
3. Because structured references are readable, auditing is easier—even managers unfamiliar with ranges can grasp the logic.
4. Copy B2:B4 down to create a matrix of different category totals. Each analyst can simply over-type B2 with “Soda,” “Water,” or “Energy” to recalculate.

How it solves business problems

• Automates category-level revenue reporting that used to require manual filters.
• Eliminates risk of forgetting to select “Retail” in a slicer each time.
• Supports live dashboards: if SalesData is connected to Power Query for nightly refresh, the totals update automatically.

Integration with other features

• Insert a PivotTable on top of SalesData and add a calculated item to reconcile your SUMIFS result, ensuring formula accuracy.
• Add conditional formatting to flag when the Retail subtotal falls below target thresholds.

Performance considerations

• SUMIFS on a Table column is fast because the ranges are contiguous. If the dataset grows toward Excel’s row limit, ensure calculations run in Automatic Except Data Tables mode to avoid delays.

Example 3: Advanced Technique

Scenario: You maintain an SKU master where Column A holds description, Column B holds warehouse location code, Column C quantity on hand. Company leadership wants a case-sensitive total of quantities for descriptions containing the brand string in cell F1 and stored in location “NW.” SUMIF is not case-sensitive, so you need a different approach.

Solution using SUMPRODUCT + EXACT + SEARCH:

=SUMPRODUCT( (EXACT(MID(A2:A200,LEN(""),LEN(A2:A200)),A2:A200)=TRUE) * (ISNUMBER(SEARCH(F1,A2:A200))) * (B2:B200="NW") * C2:C200 )

Simplified, more transparent version (array-entered in legacy Excel, spill in 365):

=SUMPRODUCT( --ISNUMBER(FIND(F1,A2:A200)), --(B2:B200="NW"), C2:C200 )

Explanation

• FIND is case-sensitive; SEARCH is not. We use FIND to respect upper/lower case.
• ISNUMBER converts FIND’s positional output into TRUE/FALSE.
• Double unary (--) coerces TRUE/FALSE into 1/0 so SUMPRODUCT can multiply.
• B2:B\200=\"NW\" adds the warehouse filter.
• SUMPRODUCT multiplies the Boolean arrays by the quantities and returns the total.

Performance optimization

• Limit the range to current data rows rather than entire columns to reduce calculation load.
• In Excel 365, wrap the Boolean logic inside FILTER first, then SUM the result:

=SUM( FILTER(C2:C200, (B2:B200="NW") * ISNUMBER(FIND(F1,A2:A200)) ) )

Error handling

• FIND returns #VALUE! if the substring is not found. ISNUMBER converts those to FALSE so the formula remains robust.
• If F1 is blank, FIND returns 1 (because it finds an empty string at position 1) and the sum returns the grand total. Prevent that with an IF wrapper: `=IF(`F\1=\"\",\"\",(formula)).

Professional tips

• Convert A:C into a Table and use structured names to make the formula self-documenting.
• Add a helper column with `=FIND(`F1,[Description]) and filter out errors before summing; this can improve performance on extremely large datasets.

Tips and Best Practices

1. Use Excel Tables for auto-expanding ranges so your SUMIF formulas never miss newly added rows.
2. Always surround the criteria with both leading and trailing asterisks unless you specifically need “starts with” or “ends with” logic.
3. Store the keyword in its own named cell (e.g., KeywordCell) and reference it to avoid accidental hard-coding.
4. For recurring reports, combine SUMIFS with dropdown Data Validation lists so non-technical users can select categories without editing formulas.
5. Use TRIM or CLEAN on imported text columns to strip hidden characters that break matching.
6. If you anticipate thousands of different keyword lookups, consider loading the data into Power Pivot and writing a simple DAX measure, which can outperform worksheet formulas at scale.

Common Mistakes to Avoid

1. Forgetting the wildcard: Writing "&F1&" instead of "*"&F1&"*" means the formula searches for an exact match and returns zero when descriptions contain extra words.
2. Placing the wildcard inside the criteria cell: Typing *Shirt* in F1 and then using "*"&F1&"*" creates "**Shirt**" and may mis-match. Keep wildcards in the formula, not in the cell.
3. Mixing text and numbers in the sum range: If any cell in the numeric column contains text like “N/A,” SUMIF skips it silently, leading to undercounted totals. Clean or convert your field first.
4. Expanding ranges to full columns in very large files: Using [A:A] in SUMIFS recalculates over a million rows each time. Restrict to actual data or use dynamic Tables.
5. Overlapping criteria such as “Pro” when “Promo” and “Product” exist: results inflate unexpectedly. Test keywords with a quick FILTER preview to ensure uniqueness.

Alternative Methods

| Method | Core Formula | Pros | Cons | Best For | | — | — | — | — | — | | SUMIF with wildcards | `=SUMIF(`text_range,\"\"&kw&\"\",sum_range) | Simple, backward-compatible, fast | Case-insensitive only, single text criterion | Most day-to-day tasks | | SUMIFS with multiple criteria | `=SUMIFS(`sum_rng,text_rng,\"\"&kw&\"\",cat_rng,cat) | Supports additional filters | Same case limitation | Multi-dimensional reports | | SUMPRODUCT with SEARCH/FIND | `=SUMPRODUCT(`--ISNUMBER(SEARCH(kw,text_rng)),sum_rng) | Case flexibility, array logic | Slightly slower, harder to read | Case-sensitive or complex conditions | | FILTER + SUM (365) | `=SUM(`FILTER(sum_rng,ISNUMBER(SEARCH(kw,text_rng)))) | Spills matching rows for audit, very flexible | 365 only | Interactive dashboards, auditing | | DAX Measure in Power Pivot | `=CALCULATE(`SUM(Revenue),FILTER...) | Handles millions of rows, joins tables | Requires Data Model, learning curve | Enterprise-scale models |

Choose SUMIF for speed and simplicity, SUMPRODUCT for advanced pattern needs, and DAX when datasets exceed worksheet capacity.

FAQ

When should I use this approach?

Use it any time your numeric metric resides in one column, your identifier is embedded inside another column’s text, and you need a live subtotal rather than a one-off filter. Typical scenarios include keyword-based sales reports, project code spend analyses, or aggregating survey responses containing specific phrases.

Can this work across multiple sheets?

Yes. Just qualify ranges with sheet names:

=SUMIF(Orders!B:B,"*"&Input!A2&"*",Orders!F:F)

For many sheets, consolidate data into a single Table or use Power Query to avoid maintaining dozens of similar formulas.

What are the limitations?

SUMIF and SUMIFS are always case-insensitive and cannot natively handle “contains” logic on multiple keywords combined with OR logic (e.g., “Shirt” or “Pants”). They also require all ranges to be the same size, so you cannot mix filtered lists of unequal length.

How do I handle errors?

• Wrap FIND in IFERROR when building case-sensitive SUMPRODUCT formulas.
• Replace error-prone text like “—” in numeric columns with zero via VALUE or CLEAN before aggregation.
• Use conditional formatting to highlight descriptions that actually matched the keyword, making it easier to debug unexpected totals.

Does this work in older Excel versions?

SUMIF with wildcards has existed since Excel 97, so compatibility is nearly universal. SUMIFS requires Excel 2007 or later. FILTER is exclusive to Excel 365 and 2021. In Office 2003 or earlier, use SUMPRODUCT.

What about performance with large datasets?

On ranges of hundreds of thousands of rows, SUMIF remains quick. However, if you embed many keyword cells each calculating across entire columns, recalc time can climb. Strategies: convert to Tables, limit to used rows, switch to manual calc mode, or offload heavy summarization to Power Pivot.

Conclusion

Knowing how to “sum if cell contains text in another cell” turns messy narrative data into actionable numbers with a single dynamic formula. By mastering SUMIF wildcards, adding SUMIFS for extra filters, and graduating to SUMPRODUCT or FILTER for specialized needs, you can deliver instant, reliable subtotals that keep pace with ever-changing business questions. Integrate these techniques into dashboards, audits, and daily ad hoc analysis to sharpen your overall Excel proficiency and free time for higher-value insights. Keep experimenting with variations, and soon spotting and summing embedded codes will become second nature in your analytical workflow.