How to Use Wildcard in SUMIFS Function in Excel

This guide will discuss how to use wildcards in the SUMIFS function in Excel. 

The rules for using the SUMIFS function in Excel is the following:

• The sum_range argument should contain numeric values. 
• The SUMIFS function can only support up to 127 different conditions. 
• When we input an array, the SUMIFS function will not work. So the function only requires actual ranges for all range arguments.
• The function is not case-sensitive. 
• If we input ranges that do not match, the function will return a #VALUE error. So each range must have the same number of rows and columns as sum_range. 
• Additionally, text strings must always be enclosed in double quotes. However, cell references do not need to be enclosed in quotes.

Excel is an excellent tool for different tasks. With the help of its several built-in functions and tools, we can perform many difficult tasks easily. When we input large data sets in Excel, it becomes difficult to remember the exact text or full text of certain data.

So it becomes a problem when we want to look or search up certain texts. Since Excel needs the full text to return or find the value, this becomes an issue. And it would take too much time to go through each value to look for specific text in a large data set.

Thus, wildcard characters are beneficial in situations like this. Basically, there are three commonly used wildcards in Excel: the asterisk, the question mark, and the tilde. And these wildcards are often paired with different functions, such as the SUMIFS function.

Let’s take a sample scenario wherein we need to use wildcards in the SUMIFS function in Excel.

Suppose you have a data set containing the product ID and price of each product. And you are tasked to obtain the total sum of those specific products. But, you do not remember the entire product IDs. So you opted to use wildcards with the SUMIFS function to find the products and get the sum. 

Great! Before we move on to a real example, let’s first learn how to write the SUMIFS function in Excel. 

The Anatomy of the SUMIFS Function

The syntax or the way we write the SUMIFS function is as follows:

=SUMIFS(sum_range, criteria_range1, criteria1)

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we activate any function in Excel.
• SUMIFS() refers to our SUMIFS function. And this function is used to add the cells we specified depending on the given set of conditions or criteria we set.
• sum_range is a required argument. So this refers to the actual cells we want to sum.
• criteria_range1 is another required argument. And this refers to the range of cells we want to be evaluated for a specific condition. 
• criteria1 is also a required argument. So it refers to the condition or criteria in the form of a number, expression, or text that will define which cells will be added.

Amazing! Now let’s dive into a real example of how to use wildcards in the SUMIFS function in Excel.

A Real Example of Using Wildcard in SUMIFS Function in Excel

Let’s say we have a data set containing information about certain products. In the first column, we have the product ID. And the second column has the price of each product. So our initial data set would look like this:

In this case, we are tasked to add only specific products. However, we do not remember the full text of the product IDs. So this is where we can utilize wildcard characters. 

Firstly, the question mark matches any one character. Secondly, the asterisk is used to match any sequence of characters. Lastly, the tilde is used to neutralize the wildcard characteristics of the question mark and the asterisk. 

If we are looking for a question mark or asterisk, we will input a tilde in front of them. Furthermore, the SUMIFS function returns the sums of cells in a range that meets the set conditions or criteria. 

For instance, we will use the asterisk to find the specific product IDs. Firstly, we will enclose the specific text in an asterisk to return a text string that contains that specific text.

Secondly, we can place the asterisk at the end to find a text string that begins with the inputted text. Lastly, we can also place an asterisk at the beginning of the inputted text to find a text string that ends with that certain text. 

In this case, we want to look for product IDs that begin with the specific text. So we will be placing the asterisk at the end of the specific text. Afterward, we can simply input this in the SUMIFS function and return the total price of the matched cells. 

So our final data set would look like this:

You can make your own copy of the spreadsheet above using the link attached below. 

Great! Now let’s discuss the steps of how to use wildcards in the SUMIFS function in Excel.

How to Use Wildcard in SUMIFS Function in Excel

In this section, we will discuss the step-by-step process of how to use wildcards in the SUMIFS function in Excel. Furthermore, each step will contain detailed instructions and pictures to guide you along the process. 

1. Firstly, we will create a new table to input the results. So let’s first try a text contained within a text string. Then, we can type the formula “=SUMIFS(C3:C7, B3:B7, “*AA*”)”. Lastly, we will press the Enter key to return the results.

2. And tada! We have successfully added the sum for cells with text within the text string.

3. Secondly, we will try to obtain the text string that starts with the inputted text. So we will input the formula “=SUMIFS(C3:C7, B3:B7, “A*”)”. Next, we will press the Enter key to return the results.

4. And tada! We have successfully used wildcards in the SUMIFS function in Excel.

5. Lastly, we will try to get the text string that ends with the inputted text. Then, we will type in the formula “=SUMIFS(C3:C7, B3:B7, “*BC”)”. Finally, we will press the Enter key to return the results.

6. And tada! We have successfully added the text strings that ended with the specific text.

And that’s pretty much it! We have explained how to use wildcards in the SUMIFS function in Excel. Now you can apply this learning to your work whenever you need to get the sum of data you do not remember the full name of. 

Are you interested in learning more about what Excel can do? You can now use the SUMIFS function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe