In Microsoft Excel, the **SUMIFS function** is used to evaluate the sum from a range of cells under multiple conditions. Adding the wildcard feature can make this function much more effective. In this article, we’ll demonstrate ** 3** easy and detailed examples of how to use the

**SUMIFS function with wildcard in Excel**. So, go through the entire article to understand the topic properly.

## Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article. Also, it’s convenient for your practice.

## 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 the full text is not provided. In that case, it will be a great hassle to fetch and do whatever reading or writing 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.

## 3 Examples to Use SUMIFS Function with Wildcard in Excel

We will use the following dataset to explain the use of the ** 3** types of

**SUMIFS**formulas with wildcards. The dataset contains the

**and**

*Product ID***of different types of products of a company.**

*Price*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 wildcard in Excel in detail in the following section of the article.

### 1. Using SUMIFS Function with Asterisk (*) Wildcard

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

**1.1 Text Contained Within a Text String**

If you want to fetch a text which is within a text string (let’s say “** NNN**”) and get the summation of the price of products having the text within their

**, you can follow the steps below:**

*Product ID***📌**** Steps:**

- At first, write the formula in cell
**E5**.

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

- Then, 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

**starts with “**

*Product ID***”, you need to follow the steps below.**

*NN***📌**** Steps:**

- This time, enter the following 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 a particular text or letter (say ** a**) and get summed price of the products having the same last text or letter in the same

**, then the steps will be:**

*Product ID***📌**** Steps:**

- Firstly, insert the formula below in cell
**E7**.

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

- As always, 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***, and**

*NNN-Gamma***because all these have “**

*NN-Sigma***” at the end.**

*a**Note:** The asterisks are on the right side when the start text or letter is to be looked up for, and vice versa for the left side asterisk*.

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

### 2. Utilizing SUMIFS Function with Question Mark (?) Wildcard

Sometimes you might not be able to remember other characters in the text, or you might 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 is 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’ exact positions within the text. Let’s see the use of **question marks (?)** for both of the types.

**2.1 Exact Number of Characters After Specific One**

Suppose you remember the first three letters “** NNN**” and you know that there are

**more letters in the text along with the space in between. The steps to find the text and sum their prices are the following.**

*6***📌**** Steps:**

- Initially, write down the formula below as shown in cell
**E5**.

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

- Then, 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 “

**”, you can fetch the text from the range using**

*m***question marks (?)**. Just follow these steps to know-how:

**📌**** Steps:**

- FIrstly, in cell
**E6**, write down the formula below.

`=SUMIFS(C5:C13,B5:B13,"N*m?*")`

- Then, to see the result, press the
**ENTER**key.

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

### 3. Incorporating SUMIFS Function with Tilde (~) Wildcard

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

- Initially, write the formula in cell
**E5**.

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

## What to Do If SUMIFS Function with Wildcard Is Not Working in Excel

Consider that we have other data in the given dataset. It has a numeric number (** 6**) as the

**. Now if we apply the formula using an asterisk along with the text:**

*Product ID*

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

This time, the formula is the following.

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

## Alternatives to Application of SUMIFS Function with Wildcard in Excel

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

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

Similarly, we can use the **Asterisk (*)** wildcard with the **SUMIF function** for several conditions.

**1.1 SUMIF with Text at Beginning, Middle or End**

At this time, the formula for this is:

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

**1.2 SUMIF with Text at Start**

At this time, the formula will be:

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

**1.3 SUMIF with Text at End**

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

- At first, enter cell
**E7**and put the formula down.

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

### 2. SUMIF Function with Question Mark (?) Wildcard

The same goes for the **question mark (?)** wildcard. See the variations below.

**2.1 SUMIF for Unknown Length of Characters**

Here the formula will be:

- Forthwith, go to cell E5 and write down the formula below.

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

**2.2 SUMIF for Character Within 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.

- Firstly, select cell
**E6**and enter the formula below.

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

- Also, press the
**ENTER**key.

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

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

- At first, go to cell
**E5**and insert the following formula.

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

- Then, tap
**ENTER**.

*Note:** The result matches the methods with the SUMIFS function. Mainly, you can use the SUMIF function 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.

## Practice Section

For doing practice by yourself, we have provided a **Practice** section like the one below on the right side of the sheet. Please do it by yourself.

## Conclusion

This article explains how to use the **SUMIFS function** with wildcard in Excel in a simple and concise manner. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, **ExcelDemy**, a one-stop Excel solution provider, to explore more.

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