SUMIFS with Wildcard in Excel + 3 Alternative Formulas

The various functions of Microsoft Excel have greatly facilitated day-to-day operations in the corporate world. One of them is the SUMIFS function which is a function used for addition using multiple criteria. Adding Excel’s Marvelous Wildcard feature can make its use much more effective. This article will explain 3 different usages of the SUMIFS function with Wildcard. Moreover, we will show 3 more alternative formulas with wildcards that we can use as alternatives to the SUMIFS formulas.

Download Practice Workbook

You can download the workbook from here.

The SUMIFS Function: an Overview

Objective:

It adds all of its arguments that satisfy multiple criteria.

Syntax:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Arguments:

Sum_range= The range of cells to sum. This argument is of the required type. That means you must enter this argument.

criteria_range1=Β  (Required) The range that is evaluated using Criteria1.

Criteria1= (Required) The criteria that define which cells in Criteria_range1 will be summed up.

Criteria_range2, criteria2, …= (Optional) Further ranges and their corresponding criteria.

πŸ”— Read More: How to Use SUMIFS Function in Excel (6 Handy Examples)


Introduction to Wildcard in Excel

Large datasets contain so much data. It is not always possible to remember the full value or text and lookup for it using different lookup formulas or techniques. Again, Excel cannot fetch the text if full text is not provided. In that case, it will be a great hassle to fetch and do whatever read or write we want to do with the data.

However, Excel Wildcards are a savior in this case. We can use these with cell references, texts, and so forth to work with partial data.

Generally, there are three types of wildcards: Asterisk (*), Question Mark (?), and Tilde (~). These are widely used with Excel functions like SUMIFS, LOOKUP, MATCH, and so on. Besides, these are also used with Excel tools like Find & Replace, Conditional Formatting, etc.

πŸ”— To Know the Use of Wildcards: How to Use Wildcards in Excel?

This article will provide knowledge regarding the usage of the 3 types of SUMIFS formulas with Excel wildcard.


3 Methods to Use SUMIFS with Wildcard in Excel

We will use the following dataset to explain the use of the 3 types ofΒ  SUMIFS formulas with Wildcard.

Excel Sumifs with wildcard :sample dataset

The dataset contains the Product ID and Price of different types of products of a company. If you want to get the sum based on criteria you have to use wildcards along with the texts for this type of dataset. We will explain the use of 3 types of SUMIFS formulas with wildcardsΒ in detail in the following section of the article.


1. SUMIFS with the Asterisk (*) Wildcard in Excel

Asterisk (*) is one of the most widely used wildcards in Excel. Let us see how to get sum based on criteria related to text using Asterisk (*) with SUMIFS.

1.1 Text Contained within a Text string

If you want to fetch a text which is within a text string ( let us say β€œNNN”) and get the summation of the price of products having the text within their Product ID (4.00+4.44), you can follow the steps below:

Steps:

  • Write the formula in Cell E5.
=SUMIFS(C5:C13,B5:B13,"*NNN*")
  • Press Enter.

The result will be as follows:

SUMIFS for text within

πŸ”ŽHow Does the Formula Works?

πŸ“Œ The range C5:C13 is the range from where the SUMIFS function will extract and sum the corresponding prices.

πŸ“Œ Next the range B5:B13 is the range from where the text contained within is fetched based on criteria.

πŸ“Œ Lastly, the criteria are taken. Here the criterion is it will fetch the texts which contain β€œNNN” within it. Asterisk (*) is present at both sides of the text to indicate the text should be within a text.

πŸ“Œ After fetching using the SUMIFS formula, the result is $8.44.

1.2 Text Starting with Certain Text

Moreover, if you want to get a text which starts with a particular text ( say β€œNN”) and get the sum of the prices of products whose product ID starts with β€œNN”, you need to follow the steps below.

Steps:

  • Write the formula in Cell E6.
=SUMIFS(C5:C13,B5:B13,"NN*")
  • Then, press Enter.

SUMIFS for text at the start

This time the formula includedΒ NN-Sigma because it starts with NN.

πŸ”ŽHow Does the Formula Work?

πŸ“Œ The range C5:C13 is the range from where the summation of particular prices will be extracted.

πŸ“Œ Next the range B5:B13 is the range from where the text at the start is fetched based on criteria.

πŸ“Œ Lastly, it takes the criteria. Here the criterion is it will fetch the texts which contain β€œNN” within it. Asterisk (*) is present at the right side of the text to indicate the text should be at the beginning of the data.

πŸ“Œ After fetching using the SUMIFS formula, the result is $16.65.

1.3 Text Ending with Certain Text

Further, if you want to get texts that end with particular text or letter (say a) and get summed price of the products having last text or letter of product ID same, then the steps will be:

Steps:

  • You have to write the formula below in Cell E7.
=SUMIFS(C5:C13,B5:B13,"*a")

SUMIFS result for text at the end

The result is the summation of the prices $(4.00+5.20+4.44+8.21) of the product ID’s NNN-Delta, PPP-Alpha, NNN-Gamma, and NN-Sigma because all these have β€œa” at the end.

πŸ”ŽHow Does the Formula Work?

πŸ“Œ The range C5:C13 is the range from where the summation of particular prices will be extracted.

πŸ“Œ Next the range B5:B13 is the range from where the text at the end is fetched based on criteria.

πŸ“Œ Lastly, it takes the criteria. Here the criterion is it will fetch the texts which contain β€œa” within it. Asterisk (*) is present at the left side of the text to indicate the text should be at the end of the date.

πŸ“Œ After fetching using the SUMIFS formula, the result is $21.85.

Notice: The asterisks are at the right side when the start text or letter is to look up for and vice versa for the left side asterisk.


2. SUMIFS with the Question Mark (?) Wildcard in Excel

Sometimes you might not be able to remember other characters in the text or you can only remember a few characters. You can still fetch the text and get its relevant summation using the question mark (?). There can be two different types of it. One where you can remember a few characters within a text and the total length of the text. The other one is where you can remember text or letters of exact position within the text. Let’s see the use of question mark (?) for both of the types.

2.1. Exact Number of Characters after a Specific One

Suppose you remember the first three letters β€œNNN” and you know that there are 6 more letters in the text along with the space in between.

Steps to find the text and sum their prices:

Steps:

  • Write the formula as shown in Cell B5.
=SUMIFS(C5:C13,B5:B13,"NNN??????")
  • After writing the formula, press Enter.

SUMIFS with Question mark for length of characters fetching and summing their prices

πŸ”ŽHow Does the Formula Work?

πŸ“Œ The range C5:C13 is the range from where the summation of particular prices will be extracted.

πŸ“Œ Next the range B5:B13 is the range from where the actual text is fetched based on criteria.

πŸ“Œ Lastly, it takes the criteria. Here the criterion is it will fetch the texts which contain β€œNNN” with 6 letters after it along with a space in between. Question marks (?) are present at the right side of the text to indicate that there are a total of 7 letters with a space in the text.

πŸ“Œ After fetching using the SUMIFS formula, the result is $8.44.

2.2. Question Mark with AsterisksΒ 

Now, if you remember that the first text is β€œN” and the text before the last one is β€œm”, you can fetch the text from the range using question marks (?).

Follow the steps to know-how:

Steps:

  • In Cell B6, write the formula:
=SUMIFS(C5:C13,B5:B13,"N*m?*")
  • To see the result press Enter.

SUMIFS with question mark for text having special character

πŸ”ŽHow Does the Formula Work?

πŸ“Œ The range C5:C13 is the range from where the summation of particular prices will be extracted.

πŸ“Œ Next the range B5:B13 is the range from where the actual text is fetched based on criteria.

πŸ“Œ Lastly, it takes the criteria. Here the criterion is it will fetch the texts which contain β€œN” at the starting and the text before the last one is β€œm”. Asterisk (*) is at the right of β€œN” to indicate that it is the first part of the text. However, a question mark (?) is put at the right side of the text β€œm” to indicate that there is a single text after this.

πŸ“Œ After fetching using the SUMIFS formula, the result is $12.65.


3. SUMIFS with the Tilde (~) Wildcard in Excel

Sequentially there is another amazing wildcard named Tilde (~). We use this to include characters in the text in exact positions while nullifying its actual feature in Excel.

From the given dataset let us consider we have a text which starts with β€œB” and contains β€œ?” at the end. Now follow the steps below to fetch the text and sum their prices.

Steps:

  • Write the formula in cell B5:
=SUMIFS(C5:C13,B5:B13,"B*~?")
  • Afterward, press Enter.

SUMIFS with Tilde

πŸ”ŽHow Does the Formula Work?

πŸ“Œ The range C5:C13 is the range from where the summation of particular prices is extracted.

πŸ“Œ Next the range B5:B13 is the range from where the text having question mark at the end is fetched based on criteria.

πŸ“Œ Lastly, it takes the criteria. Here the criterion is it will fetch the texts which contain β€œB” at the starting and the text at the last is β€œ?”.Asterisk (*) is at the right of β€œN” to indicate that it is the first part of the text. However, a question mark (?) is put at the right side of the text β€œm” to indicate that the actual text contains a question mark at the end.

πŸ“Œ After fetching using the SUMIFS formula, the result is $16.40.

 

SUMIFS with Wildcard Not Working?

Consider we have another data with the given dataset. It has a numeric number (6) as the product ID. Now if we apply the formula using an asterisk along with the text:

=SUMIFS(C5:C14,B5:B14,6&"*")

It will show zero as the result. Because wildcards don’t work on numeric values.

Wildcards not working with numeric value

In the case of numeric values, you can simply write the numeric value (6) as a criteria argument.

The formula is:

=SUMIFS(C5:C14,B5:B14,"6")

SUMIFS without wildcard for numeric value

Alternative Methods to Sum with Wildcard in Excel

SUMIF can be an alternative option with wildcards if there is only one criterion. In this case, the arguments will be all the same as SUMIFS except that you have to replace the position of the arguments according to the syntax of SUMIF.

The SUMIF Function:

Objective:

It adds all of its arguments that satisfy a single criterion.

Syntax:

SUMIF(, criteria_range, criteria, [sum_range])

Arguments:

criteria_range=Β  (Required) The range to evaluate using criteria.

criteria= The criteria that define which cells in criteria_range, to sum up in return.

sum_range= The range of cells to sum. This argument is of the required type. That means you must enter this argument.

πŸ”— Read More: How to Use SUMIF Function in Excel (With 5 Easy Examples)

1. SUMIF with the Asterisk (*) Wildcard

1.1 SUMIF with the Text within

The formula for this:

=SUMIF(B5:B13,"*NNN*",C5:C13)

SUMIF result with asterisk for text within

1.2 SUMIF with Text at the Start

For this the formula will be:

=SUMIF(B5:B13,"NN*",C5:C13)

SUMIF with asterisk for text at the start

1.3 SUMIF with Text at the End

Formula to fetch text with text at the end and sum their prices:

=SUMIF(B5:B13,"*a",C5:C13)

SUMIF with asterisk for text at the end


2. SUMIF with the Question Mark (?) Wildcard

2.1 SUMIF for Unknown Length of Characters

Here the formula will be:

=SUMIF(B6:B14,"NNN??????",C6:C14)

SUMIF with question mark for length of characters in text

2.2 SUMIF for Character within the Text

If any special character is present within the text we can fetch them and get the sum of their prices using the formula below.

=SUMIF(B6:B14,"N*m?*",C6:C14)

SUMIF with question mark for special character


3. SUMIF with the Tilde (~) Wildcard

The formula for the SUMIF including Tilde (~) :

=SUMIF(B5:B13,"B*~?",C5:C13)

SUMIF with tilde

Note: The result matches the methods with SUMIFS. Mainly, you can use SUMIF only if you have a single criterion otherwise you cannot use this as an alternate method.


Things to Remember

You have to put the wildcards accordingly to get the exact result. If there is no text in the dataset with an exact match of what you gave as criteria, then you will get zero as a result.


Conclusion

The article explains the use of 3 different types ofΒ SUMIFS formulas with Excel Wildcard and SUMIF as an alternative for single criteria. There are three types of wildcards namely Asterisk (*), Question Mark (?), and Tilde (~). Excel SUMIFS function with Wildcard helps to fetch texts and get the sum of relevant things. I hope the article was helpful to you. For any other query please write in the comment section.


Related Articles:

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo