# How to Use SUMIFS Function with Wildcard in Excel (3 Examples)

### Example 1: Using SUMIFS Function with Asterisk (*) Wildcard

1.1 Text Contained Within a Text String

Steps:

• Insert 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

Steps:

• Insert the following formula in cellÂ E6.
`=SUMIFS(C5:C13,B5:B13,"NN*")`
• Press ENTER.

The formula included NN-Sigma because it starts with NN.

1.3 Text Ending with Certain Text

Steps:

• Insert the formula below in cell E7.
`=SUMIFS(C5:C13,B5:B13,"*a")`
• Press ENTER.

The result is the sum of the prices.

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.

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

2.1 Exact Number of Characters After Specific One

Steps:

• Insert the formula below as shown in cell E5.
`=SUMIFS(C5:C13,B5:B13,"NNN??????")`
• Press ENTER.

2.2 Question Mark with Asterisks

Steps:

• Insert the formula below in cell E6.
`=SUMIFS(C5:C13,B5:B13,"N*m?*")`
• Press the ENTER key.

### Example 3: Incorporating SUMIFS Function with Tilde (~) Wildcard

Steps:

• Insert the formula in cell E5.
`=SUMIFS(C5:C13,B5:B13,"B*~?")`
• Press ENTER.

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

Steps:

• Insert the following formula.
`=SUMIFS(C5:C14,B5:B14,6&"*")`

It will show zero as a 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.

Use the following formula.

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

### Alternative forÂ SUMIF Function with Asterisk (*) Wildcard

1.1 SUMIF with Text at Beginning, Middle or End

The formula for this is:

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

1.2 SUMIF with Text at Start

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:

• Input the formula below in cell E7 and press ENTER.
`=SUMIF(B5:B13,"*a",C5:C13)`

### Alternatives for SUMIF Function with Question Mark (?) Wildcard

2.1 SUMIF for Unknown Length of Characters

• Insert the formula below in cell E5 and press ENTER.
`=SUMIF(B6:B14,"NNN??????",C6:C14)`

2.2 SUMIF for Character Within Text

• Insert the formula below in cellÂ E6.
`=SUMIF(B6:B14,"N*m?*",C6:C14)`
• Press the ENTER key.

### Alternatives for SUMIF Function with Tilde (~) Wildcard

• Insert the formula below in cellÂ E5.
`=SUMIF(B5:B13,"B*~?",C5:C13)`
• Press ENTER.

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

## Related Articles

<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Syeda Fahima Nazreen

SYEDA FAHIMA NAZREEN is an electrical & electronics engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Proteus, MATLAB, Multisim, AutoCAD, Jupiter Notebook, and MS Office, going beyond the basics. With a B.Sc in Electrical & Electronic Engineering from American International University, Bangladesh, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF