Function Problems

How to Use SUMIFS with Wildcards to Sum Values by Text Suffix or Prefix

Maira Mehtab Sep 21, 2026 874 views

Question details

Calculate total values in a column by matching specific text patterns, such as a state abbreviation suffix or a specific prefix, in a corresponding text column.

Product: Spreadsheet
Device & OS: not provided
Scenario: A dataset contains merged text identifiers like city names with state suffixes (e.g., 'San Diego_CA') or part names with prefixes (e.g., 'S_wrench'). The goal is to sum the associated values based on just that prefix or suffix.
Observed behavior: Users need the correct formula syntax to extract or conditionally match partial text strings within SUMIF/SUMIFS functions to aggregate the correct totals.

Before you start

Ensure that the strings in your criteria column are formatted as Text and that the separator (such as an underscore) is consistently used across all data entries.

Solution 1Recommended

Use SUMIFS with a Wildcard Criterion

Use the asterisk (*) wildcard within your SUMIFS criteria to match any sequence of characters before or after your target text.

The SUMIFS function does not allow embedding functions like RIGHT directly on the criteria range. Instead, using wildcards is the most efficient way to match partial text strings.

Summing by Suffix

To sum values where the criterion is a suffix (e.g., matching the state 'CA' in cell C9 for 'City_CA'), select your result cell and enter the formula: =SUMIFS(B10:B1000, A10:A1000, "*_"&C9)

Summing by Prefix

To sum values where the criterion is a prefix (e.g., part names starting with 'S_'), use the formula: =SUMIFS(B10:B1000, A10:A1000, "S_*")

Calculate the Result

Press Enter to calculate. The asterisk acts as a placeholder for any preceding text (in the first example) or any trailing text (in the second example).

Wildcard Flexibility: You can hardcode the string like "*_CA" or dynamically link it to another cell using the ampersand (&) operator, such as "*_"&C9.

Solution 2

Dynamically Group and Sum Using GROUPBY and TEXTAFTER

For newer spreadsheet versions supporting dynamic arrays, group and sum the data directly by extracting the text after the delimiter.

Easily Manage Complex Data and Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional functions like SUMIF, SUMIFS, and wildcards. It makes calculating totals based on specific text conditions effortless, providing a familiar interface for seamless data analysis.

1. Open Your Dataset: Launch WPS Spreadsheet and open the document containing your text and values columns.
2. Insert the Function: Click on the 'Formulas' tab and select 'Insert Function', or simply type '=' in an empty cell.
3. Apply SUMIFS with Wildcards: Enter your SUMIFS formula using the asterisk (*) wildcard, select your ranges, and press Enter to instantly calculate your conditional totals.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in function wizard helps you easily construct error-free SUMIF and SUMIFS formulas.Supports extensive wildcard filtering for advanced text condition matching.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the RIGHT function inside SUMIFS to match suffixes?

No, SUMIFS requires a cell range for its criteria argument and does not support embedding data manipulation functions like RIGHT directly on that range. Instead, use wildcard characters (like '*_CA') or create a helper column using the RIGHT function, then apply SUMIFS to that helper column.

Why is my SUMIFS formula with wildcards returning 0?

This usually happens if the criteria text does not exactly match the cell contents due to hidden characters or if the separator is different. Check that your separator (e.g., underscore) matches exactly, and use the TRIM function to ensure there are no hidden trailing spaces in your data.

Do wildcards work with the regular SUMIF function as well?

Yes, both SUMIF and SUMIFS support the use of wildcards. You can use the asterisk (*) to represent any number of characters and the question mark (?) to represent a single specific character in both functions.

Excel & Data Problems