SUMIF is one of my favorite Excel functions and I’m sure it’s yours also.
But one thing which I always worry about it, we can only sum values when criteria fully match with values.
1. Asterisk [*] with SUMIF
Asterisk is the most popular wildcard character. And, it’s the one which can be used with SUMIF perfectly.
Asterisk: A Quick Intro
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 product name and invoice number is 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.
So it’s time to use an asterisk and the formula would be:
In the above formula, we have combined product name with an asterisk wildcard character sum values for each product.
So, when SUMIF matches criteria from the invoice column it takes the characters before the asterisk and replaces rest of the characters.
In simple words, it ignores all the characters after the product name from a cell, 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
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 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 comparing to an asterisk but even then it’s important to learn and can be used in some specific situations.
Have a look at 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 first and the last name you have different characters.
And if, you want to sum all the bonuses which are there you can’t simply refer to my name.
Even the name is there in several cells but each one is unique and to solve this problem and sum all the bonuses you can use below formula.
Now here you have used a question mark between first and the last name.
The thing is when you use a question mark it replaces only that character with any of the character which is available.
When SUMIF use criteria which 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
But if you work includes to managing most dirty data then a tilde could be helpful for you someday.
Tilde: A Quick Intro
In simple words, 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.
…read the example next.
Example: SUMIF + Tilde
Here we are using same data like we have in 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 below example where we have simply used the criteria to sum bonus values.
If you look at the result of the formula it shows the sum 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 text in the criteria is “Puneet”.
As I said, we need to use a tilde with the asterisk to get the sum of values. So the formula would be:
So when you use tilde next to the asterisk, SUMIF take it as a real character instead of a wildcard character.
You got the right sum of the values.
Download this sample file from here to learn more.
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 original values.
- If you have a data where you are not sure about the full-text string you can use an asterisk.
- And, if you want to replace a specific count of characters then a question mark can be used.
- But, as I said, your work includes to managing most dirty data then a tilde could be helpful for you.
I hope you 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.
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.