How to Use SUMIFS with Partial Match and Wildcards in Excel, by Leila Gharani on YouTube. 8 min 37 s.
- Video length
- 8 min 37 s
- Time to do it
- 20 minutes
- Difficulty
- Medium
- Steps
- 9
What you need
Steps
Set up your data with the text you want to match in one column and the numbers to add in another. Give each column a clear header so you can reference whole ranges.
Start the formula with =SUMIFS(sum_range, criteria_range, criteria). Put the number column first, then the text column, then your match rule.
To sum where the text contains a value anywhere, wrap it in asterisks. For example "*AT*" matches any cell that has AT at the start, middle or end.
To match text that starts with a value, put the asterisk only at the end, like "AT*". To match text that ends with it, put the asterisk only at the front, like "*AT".
Use the question mark as a fixed single-character placeholder. Two question marks mean exactly two characters can be anything, so "??10*" fixes 10 at the third and fourth positions.
To match a value at a set position from the left, count the characters and place that many question marks before it. For the fifth and sixth characters, use four question marks then the value.
For matches based on both the first two and last two characters, build the criteria with LEFT and RIGHT joined by an ampersand, then add asterisks so SUMIFS treats it as a contains match.
Add more conditions by repeating a criteria range and criteria pair inside the same SUMIFS. Each pair must have the same number of rows as the sum range.
Press Enter and check the result against a small manual count. If it looks wrong, test the wildcard part alone in a cell to see what it matches.
Tips and common mistakes
The asterisk stands for zero or more characters, so "*AT*" also matches a cell that is just AT.
The question mark stands for exactly one character, so count positions carefully before you build the pattern.
Wildcards only work on text values, not on numbers stored as numbers.
If your data uses a real question mark or asterisk as text, put a tilde before it to search for the literal character.
Questions
What is the difference between * and? in SUMIFS?
The asterisk matches any number of characters, including none. The question mark matches exactly one character, so it is useful for fixing a value at a set position.
Can I use SUMIFS with more than one partial match?
Yes. Add another criteria range and criteria pair inside the same formula, each with its own wildcard pattern, as long as all ranges are the same size.
Why does my wildcard formula return zero?
Check that the text column is really text and not numbers, and that the wildcard is inside quotes. Also confirm the sum range and criteria range cover the same rows.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
