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 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.

data to get invoice wise totals using sumif with wildcard characters

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:

=SUMIF(invoice_column,product_name&"*",amount_column)
formula using asterisk wildcard character with sumif to get invoice wise total

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.

data to get score using wildcard characters with sumif

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.

=SUMIF(name_column,“Puneet?Gogia”,amount_column)
formula combining sumif with wildcard characters question mark

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