Function Problems

How to Sum Values by Partial Text Match in Excel

WPS Content Manager Sep 28, 2026 868 views

Question details

The user needs to sum values from a numerical column based on a partial text match (such as partial client names) located in another column.

How to Sum Values by Partial Text Match in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating totals for specific items or clients where the reference data only contains a partial string or includes extra delimiters like hyphens.
Observed behavior: The user wants to aggregate data effectively by either using wildcards for string patterns or extracting partial text dynamically to group and sum values.

Before you start

Identify the exact columns containing your criteria (e.g., client names) and the values you wish to sum. Make sure your data contains consistent delimiters if you plan to use text extraction functions.

Solution 1Recommended

Use SUMIFS with Wildcards for Partial Matching

This is the most compatible and straightforward method to sum values based on a partial text match using wildcards.

The SUMIFS function allows you to conditionally sum data. By using the asterisk (*) wildcard, you can tell Excel to look for a specific substring anywhere within the text.

Select the target cell

Click on an empty cell where you want the calculated total sum to appear.

Enter the SUMIFS formula

Type the formula combining the sum range, criteria range, and your wildcard criteria. For example: =SUMIFS($B$2:$B$6, $A$2:$A$6, "*"&C2&"*").

Adjust the cell ranges

Replace $B$2:$B$6 with your column containing numerical values, $A$2:$A$6 with the column containing the text to search, and C2 with the cell containing your partial text.

Calculate the result

Press Enter to calculate the sum. The asterisks (*) act as wildcards to match any text before or after your specified string.

Use SUMIFS with Wildcards for Partial Matching

Formula Tip: If you are hardcoding the text instead of referencing a cell, you can write the criteria directly as "*text*". For example: =SUMIFS(B:B, A:A, "*Smith*").

Solution 2

Use GROUPBY and TEXTAFTER in Newer Excel Versions

Ideal for users of modern Excel versions looking to dynamically group and sum extracted text (e.g., isolating text after a hyphen).

Efficient Data Calculation in WPS Spreadsheet

Use WPS Office to Sum Values with Partial Text Matches

WPS Spreadsheet fully supports advanced formulas, including wildcard SUMIFS, allowing you to seamlessly process and analyze your data. It's a lightweight, free alternative that ensures perfect compatibility with your existing Excel workbooks.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open the workbook containing the data you want to summarize.
2. Select a cell for the total: Click on the blank cell where the partial text sum should be displayed.
3. Input the SUMIFS wildcard formula: Type =SUMIFS() and select your ranges, using asterisks (*) around the reference cell for the partial match.
4. Press Enter to calculate: Hit Enter on your keyboard. WPS Spreadsheet will instantly calculate and display the summed values.

Fully supports SUMIFS and wildcard matching for complex data analysis.100% compatible with Microsoft Excel (.xlsx) formulas and formatting.Lightweight and fast, even when handling large datasets.Free to use with a familiar, easy-to-navigate tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use multiple partial text conditions in SUMIFS?

Yes, SUMIFS allows multiple criteria. You can add additional ranges and wildcard conditions within the same formula, such as =SUMIFS(B:B, A:A, "*text1*", C:C, "*text2*").

Why is my SUMIFS wildcard formula returning 0?

This usually happens if the criteria range doesn't match the sum range in row size, or if there are unexpected characters. Ensure your ranges are perfectly aligned and double-check your concatenation syntax (e.g., "*"&C2&"*").

Does the wildcard in SUMIFS match case-sensitive text?

No, standard wildcard matches in SUMIFS are not case-sensitive. It will match 'Client' and 'client' equally. For case-sensitive partial sums, you would need to combine SUMPRODUCT, EXACT, and ISNUMBER functions.

What if I need to match text that ends with a specific word?

If you only want to match text ending with a specific string, place the wildcard only at the beginning. For example, use "*"&C2 in your criteria argument instead of putting an asterisk on both sides.

Excel & Data Problems