Adding the **Wildcard** feature can make the **SUMIFS** function in Excel much more effective.Â This article will explain 3 different usages of the **SUMIFS** function with Wildcard. Moreover, we will show 3 more alternative 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.

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

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

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:

**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:**

- Enter the formula in
**Cell E6.**

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

- Then, press
**Enter.**

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

**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")`

- Then pressÂ
**Enter**.

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.

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

**Read more:** **SUMIFS Multiple Criteria Different Columns**

**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 marks (?) **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 are the following.

**Steps:**

- Write the formula as shown in
**Cell B5.**

`=SUMIFS(C5:C13,B5:B13,"NNN??????")`

- After writing the formula, press
**Enter.**

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

**Read More: Search for Question Mark in Excel (4 Suitable Methods)**

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

**Read More:** **SUMIFS Sum Range Multiple Columns in Excel(6 Easy Methods)**

**Similar Readings**

**Use SUMIFS with Multiple Criteria in the Same Column (5 Ways)****How to Use COUNTIF with Wildcard in Excel (7 Easy Ways)****SUMIFS Multiple Criteria Along Column and Row in Excel****How to Find And Replace Values Using Wildcards in Excel**

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

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")`

**Alternative to SUMIFS 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.

### 1. SUMIF with the Asterisk (*) Wildcard

**1.1 SUMIF with the Text within**

The formula for this:

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

**1.2 SUMIF with Text at the Start**

For this the formula will be:

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

**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)`

### 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)`

**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)`

### 3. SUMIF with the Tilde (~) Wildcard

The formula for the **SUMIF** including **Tilde (~) :**

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

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

**Read More:** **Excel Match Wildcard in Lookup Array (with 3 Formulas)**

**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 queries please write in the comment section.

**Related Articles**

**VLOOKUP with Wildcard in Excel (3 Methods)****How to Perform VLOOKUP with Wildcard in Excel (2 Methods)****SUMIF with Multiple Criteria in Column & Row in Excel (Both OR and AND Type)****How to Use SUMIFS formula with Multiple Criteria in Excel (11 Ways)****INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)**