How to How to Use SUMIFS with Wildcards in Excel in Excel - Practical Guide

Learn to combine SUMIFS with wildcard characters (* and ?) to sum values based on partial text matches. This technique enables flexible criteria matching without exact phrase requirements, making data analysis faster when working with inconsistent naming conventions or partial identifiers.

Why This Matters

Wildcards expand SUMIFS capabilities to handle real-world data variations and incomplete criteria, saving hours of manual filtering and allowing dynamic report automation.

Prerequisites

•Basic understanding of SUMIFS formula structure and syntax
•Familiarity with cell references (A1, B2:B10) and ranges

Step-by-Step Instructions

Open your data workbook

Launch Excel and open the file containing your data with values to sum and criteria columns to match.

Identify your sum range and criteria ranges

Determine which column contains values to sum and which column(s) contain criteria text for matching.

Click on an empty cell for your formula

Select the cell where you want the result displayed (e.g., D15) and click to activate it.

Enter SUMIFS formula with wildcard syntax

Type =SUMIFS(sum_range, criteria_range1, "*text*") using * before/after text for partial matches or ? for single characters.

Press Enter and verify results

Press Enter to execute the formula; check that the sum correctly matches all rows containing your partial criteria text.

Alternative Methods

Using SUMPRODUCT with ISNUMBER and SEARCH

Combine SUMPRODUCT, ISNUMBER, and SEARCH functions for case-sensitive wildcard matching when SUMIFS wildcards are insufficient.

Multiple SUMIF statements with addition

Chain separate SUMIF formulas with + operators to sum multiple criteria, though less elegant than SUMIFS with wildcards.

Tips & Tricks

✓Use * to match any number of characters and ? to match exactly one character in criteria strings.
✓Surround wildcard patterns with quotation marks ("*pattern*") to ensure Excel treats them as text criteria.
✓Combine multiple SUMIFS statements with + operators to sum based on multiple wildcard criteria in different columns.

Pro Tips

★Use wildcards at the beginning (*text) to catch variations in product codes or at the end (text*) for evolving category names.
★Nest SUMIFS with wildcard criteria inside IF statements to create dynamic conditional calculations based on cell values.
★Test wildcard formulas with complex data by using Data > AutoFilter first to visually confirm which rows should be included.

Troubleshooting

Formula returns 0 even though matching rows exist

Verify the sum_range contains numbers, not text. Use VALUE() to convert text to numbers if necessary.

Wildcard criteria not matching expected rows

Check for extra spaces in data by using TRIM(). Verify the exact text position in the criteria column.

#VALUE! error appears in the formula cell

Ensure all range references are properly formatted and criteria text is enclosed in quotes with wildcards.

Related Excel Formulas

SUMIF SUMPRODUCT SEARCH ISNUMBER IF

Frequently Asked Questions

Can I use wildcards with multiple criteria ranges in SUMIFS?

Yes, you can apply wildcards to any criteria_range argument. For example: =SUMIFS(A:A, B:B, "*text*", C:C, "*other*") sums column A where column B contains 'text' and column C contains 'other'.

Is SUMIFS case-sensitive when using wildcards?

No, SUMIFS with wildcards is case-insensitive by default. Use SUMPRODUCT with SEARCH for case-sensitive matching.

What's the difference between * and ? wildcards?

The asterisk (*) matches zero or more characters, while the question mark (?) matches exactly one character. Use "S?les" to match 'Sales' or 'Soles'.