Sum Values for Multiple Partial Text Matches in Excel
Question details
Sum values in column B when text in column A partially matches search terms listed in column D.

- 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.
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.
Use a Spilled SUMIF Formula in Microsoft 365
Use this method to return a separate sum for each term with one formula.
Select E2 and enter =SUMIF($A$2:$A$5,"*"&D2:D5&"*",$B$2:$B$5). Press Enter to spill the results down.
To return one total instead, enter =SUM(SUMIF($A$2:$A$5,"*"&D2:D5&"*",$B$2:$B$5)) in an empty cell.

Copy a SUMIF Formula for Each Search Term
Use this method when your Excel version does not support spilled array formulas.
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. Open the workbook: Open your Excel workbook in WPS Spreadsheet and locate the text, values, and search terms.
- 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. Fill down: Drag the fill handle from E2 down alongside the search terms to calculate each result.
- 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.

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.




