Function Problems

Sum Values for Multiple Partial Text Matches in Excel

Ayan MasoodAyan Masood Nov 18, 2026 869 views

Question details

Sum values in column B when text in column A partially matches search terms listed in column D.

Sum Values for Multiple Partial Text Matches in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains text and corresponding numeric values, plus a separate list of terms to search for within the text.
Observed behavior
The user wants a sum for each search term or a combined total across all terms.
Before you start

Check that A2:A5 contains the text to search, B2:B5 contains numeric values, and D2:D5 contains nonblank search terms. Adjust the ranges in the formulas to match your worksheet and leave enough empty cells below E2 for any spilled results.

Solution 1Recommended

Use a Spilled SUMIF Formula in Microsoft 365

Use this method to return a separate sum for each term with one formula.

1
Enter the formula

Select E2 and enter =SUMIF($A$2:$A$5,"*"&D2:D5&"*",$B$2:$B$5). Press Enter to spill the results down.

2
Calculate a combined total

To return one total instead, enter =SUM(SUMIF($A$2:$A$5,"*"&D2:D5&"*",$B$2:$B$5)) in an empty cell.

Use a Spilled SUMIF Formula in Microsoft 365
Check overlapping matches: The combined total adds the result for each term. If one row matches multiple terms, or a term appears more than once, its value can be counted multiple times.
WPS Spreadsheet

Sum Partial Text Matches in WPS Spreadsheet

Use SUMIF in WPS Spreadsheet to calculate a total for each search term, then add the results if you need a combined total.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and locate the text, values, and search terms.
  2. 2. Enter SUMIF: Select E2 and enter =SUMIF($A$2:$A$5,"*"&D2&"*",$B$2:$B$5). Adjust the ranges to fit your data.
  3. 3. Fill down: Drag the fill handle from E2 down alongside the search terms to calculate each result.
  4. 4. Add the results: Enter =SUM(E2:E5) in an empty cell for a combined total, noting that overlapping matches can be counted more than once.
Find partial text matches with SUMIF wildcardsCalculate separate totals for multiple search termsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

How does the formula find partial matches?

In the SUMIF criterion, "*"&D2&"*" places a wildcard before and after the term in D2, matching text that contains the term anywhere.

Why is the combined total larger than expected?

A row can be counted for each matching term. Check column D for duplicate or overlapping terms, and check whether a single cell in column A contains more than one term.

What happens if a search term is blank?

A blank term produces the criterion "**", which can match text broadly. Remove blank cells from the search-term range before calculating the totals.

Why does Excel show a spill error?

The Microsoft 365 formula needs empty cells below E2 for its results. Clear any cells blocking the spill range and enter the formula again.