SUMIF’s criteria accepts three wildcards. The asterisk stands for any number of characters, so =SUMIF(A2:A20,"Mobile*",B2:B20) sums every row starting with “Mobile”, and "*Mobile*" matches it anywhere in the cell.
To take the text from a cell rather than typing it, join with &: =SUMIF(A2:A20,D1&"*",B2:B20). The question mark stands for exactly one character, so "Pun??t" matches Punnet and Puniit but not Punt.
The tilde cancels a wildcard when your data contains a literal * or ? — place it immediately before the character it escapes, so =SUMIF(A2:A20,"Puneet~?",B2:B20) matches the text "Puneet?" rather than treating the question mark as a wildcard.
Two limits worth knowing: wildcards only work on text criteria, so they do nothing against numbers or dates, and they can’t be combined with comparison operators like > or <. Everything here works identically in SUMIFS, COUNTIF and COUNTIFS.
SUMIF is one of my favorite Excel functions and I’m sure it’s yours also. But one thing which I always worry about is that we can only sum values when the criteria fully match the values.
Let’s say your name is John Martin.
In this case, if we want to sum values using your name, we should have your full name. And if we just have your first name, we can’t use it.
Yes, it’s a problem. But, I have a solution for this as well. Do you remember Excel’s wildcard characters? Yes, you got it right.
If we use wildcard characters with SUMIF, we can sum values using partial criteria. The good news is we can use all three characters (Asterisk [*], Question Mark [?], Tilde [~]) with SUMIF/SUMIFS.
Today in this post, I’d like to share with you a simple way to use SUMIF with wildcard characters explaining the usage of all three characters with examples.
If you want to learn about wildcard characters, you can learn from this guide and make sure to download this data file from here to follow along.
And now, let’s get started…
1. Asterisk [*] with SUMIF
Asterisk is the most popular wildcard character. And, it’s the one that can be used with SUMIF perfectly.
Asterisk: A Quick Intro
You can use an asterisk to represent any number of characters (including none) at the end, the start or in the middle of the criteria.
As I said, if we have John as the criteria and we need to sum values for the name John Martin, we can combine the text “John” with an asterisk to represent the rest of the text.
Example: SUMIF + Asterisk
Here I have a better example for you to understand the working of asterisk with SUMIF.
Below is the table where we have a column where the product name and invoice number are joined, or you can say it’s a product-wise invoice number.

Now, from this table, we need to create a product-wise total. But if you see, every single product name is unique because we have an invoice number with it.
From here even if we create a pivot table, we can’t get a product-wise total. So it’s time to use an asterisk, and the formula would be:
=SUMIF(invoice_column,product_name&"*",amount_column)

In the above formula, we have combined the product name with an asterisk wildcard character to sum values for each product.
So, when SUMIF matches the criteria from the invoice column it matches the characters before the asterisk and lets the asterisk stand for the rest of the characters.
In simple words, it ignores all the characters after the product name from a cell and returns the sum from the amount column.
This way we are able to get the product-wise total even when we don’t have a proper product name in the column.
2. Question Mark [?] with SUMIF
A question mark is the next important character after the asterisk. With SUMIF/SUMIFS, it helps you create partial text criteria.
Question Mark: A Quick Intro
You can use a question mark to represent an unknown single character in a text string.
My name is “PUNEET”. But what if someone writes it as PUNNET and PUNIIT? I’m not able to SUM any value related to these two or any other.
If I use PUN??T (two question marks), it helps me SUM values for all three spellings.
Example: SUMIF + Question Mark
Normally, the use of a question mark is not that frequent compared to an asterisk, but even then, it’s important to learn and can be used in some specific situations.
Have a look at the below data (again I’m using my name). If you look carefully, you’ll see my name is there several times on the list.

But the point to be noted is that instead of having a space between the first and the last name, you have different characters.
And if you want to sum up all the bonuses which are there, you can’t simply refer to my name.
Even though the name is there in several cells, each one is unique. To solve this problem and sum all the bonuses, you can use the below formula.
=SUMIF(name_column,"Puneet?Gogia",amount_column)

Now here you have used a question mark between the first and last name. The thing is, when you use a question mark, it stands for exactly one character at that position, whatever that character is.
When SUMIF uses the criteria that you have specified, it only matches the first and last name and ignores the character in the matching values at the same position where you have used a question mark. The match is also not case-sensitive.
So the crux is this: A question mark represents a single character in a text string.
3. Tilde [~] with SUMIFS
I’m not sure if you will ever require using a tilde as a wildcard character. But if your work includes managing the dirtiest data, then a tilde could be helpful for you someday.
Tilde: A Quick Intro
In simple words, a tilde nullifies the effect of wildcard characters (Asterisk and Question Mark).
For example, if you are trying to sum values using a text in which you have an asterisk or a question mark as a real character, SUMIF will treat them as a wildcard instead of a real character.
Example: SUMIF + Tilde
Here we are using the same data as we have in the above example, and in this data, again you have my name. But this time, the name already has a wildcard character (an asterisk) with it.
From this data, you need to sum the bonus values for the name where you have an asterisk. Have a look at the below example where we have simply used the criteria to sum bonus values.
But: If you look at the result of the formula it shows the sum of all the bonus values (for first name and full name).

SUMIF considers that asterisk in the criteria as a wildcard and returns the sum of the bonus values for every name that starts with “Puneet”. As I said, we need to use a tilde before the asterisk to get the sum of values. So the formula would be:
=SUMIF(name_column,"Puneet~*",amount_column)

So when you use a tilde right before the asterisk, SUMIF takes it as a real character instead of a wildcard character. In the same way, use ~? for a real question mark and ~~ for a real tilde.
Problem solved. You got the right sum of the values.
SUMIF Contains Text from a Cell
Most of the time, the partial text you want to match is in a cell. Join the wildcard to the cell reference with an ampersand. The following formula sums the amounts where the name contains the text from cell D2 anywhere in it:
=SUMIF(name_column,"*"&D2&"*",amount_column)
And with SUMIFS, you can use wildcards in more than one condition. The following formula sums the amounts where the name starts with the text in D2 and the region ends with “East”:
=SUMIFS(amount_column,name_column,D2&"*",region_column,"*East")
Things to Know About Wildcards in SUMIF
- Text only: wildcards work only with text. A criterion like “100*” doesn’t match the number 1001, only the text “1001”.
- Not case-sensitive: “puneet*” and “PUNEET*” give the same result.
- Other functions: the same wildcards work in SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MATCH, VLOOKUP and XLOOKUP (with match_mode 2).
Sample File
Download this sample file from here to learn more.
Conclusion
The thing which I like about using wildcard characters with SUMIF/SUMIFS is that it saves time, and you can sum values without making any changes to the original values.
Just remember:
- If you have data where you are not sure about the full text string, you can use an asterisk.
- And if you want to replace a specific number of characters, then a question mark can be used.
- But, as I said, if your work includes managing the dirtiest data, a tilde could be helpful for you.
I hope this tip will help you get better at formulas. You can learn some of the most amazing Excel formulas from here.
Now tell me one thing.
Have you ever used SUMIF with wildcard characters before?
Share your views with me in the comment section; I’d love to hear from you. And please don’t forget to share this tip with your friends.
Frequently Asked Questions
Why does my SUMIF with a wildcard return 0?
The most common reasons are numbers in the criteria range (wildcards only match text), extra spaces in the cells, or a tilde in the wrong place. The tilde has to come right before the * or ? you want to treat as a real character.
Can I use a wildcard in the middle of the criteria?
Yes. For example, “J*n” matches any text that starts with J and ends with n, like John, Jon and Jason.
How do I sum values that do not contain a text?
Put the not-equal operator before the wildcard, like “<>*Gogia*”. SUMIF then sums the values where the name doesn’t contain “Gogia”.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.