How to use SUMIF with Wildcard Characters in Excel

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, we can only sum values when criteria fully match with 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 get 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 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 present one or more missing characters at the end or start 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 present the rest of the text.’ with an asterisk to present 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 in joint 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 sum values for each product.

So, when SUMIF matches the criteria from the invoice column it takes the characters before the asterisk and replaces 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 the next important character after the asterisk. SUMIF/SUMIFS can help you to create partial text criteria.

Question Mark: A Quick Intro

You can use a question mark to present 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 to SUM values from both of the criteria.

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 serval times on the list.

But the point to be noted is instead of having space in 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 but 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 replaces only that character with any of the characters which are available.

When SUMIF uses criteria that you have specified it only matches the first and last name and ignores the characters in the matching values for the same position where you have used a question mark.

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 name already has a wildcard character with it.

From this data, you need to sum the bonus values with the name where you have a question mark. 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 question mark in the criteria as a wildcard and returns the sum of the bonus values where the text in the criteria is “Puneet”. As I said, we need to use a tilde with an asterisk to get the sum of values. So the formula would be:

=SUMIF(name_column,"Puneet*~",amount_column)

So when you use a tilde next to the asterisk, SUMIF takes it as a real character instead of a wildcard character.

Problem solved. You got the right sum of the values.

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 it saves time and you can sum values without making any initial changes in the original values.

Just remember:

1. If you have data where you are not sure about the full-text string you can use an asterisk.
2. And, if you want to replace a specific count of characters then a question mark can be used.
3. But, as I said, your work includes managing most dirty data so 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 ever 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.