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.

• Press ENTER.

The result will be as follows:

1.2 Text Starting with Certain Text

Steps:

• Insert the following formula in cell E6.

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

• 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, 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.

• Press ENTER.

2.2 Question Mark with Asterisks

Steps:

• Insert the formula below in cell E6.

• Press the ENTER key.

Read More: SUMIFS with INDEX-MATCH Formula Including Multiple Criteria

Example 3 – Incorporating SUMIFS Function with Tilde (~) Wildcard

Steps:

• Insert the formula in cell E5.

• Press ENTER.

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

Steps:

• Insert the following formula.

It will show zero as a result. Because wildcards don’t work on numeric values.
In the case of numeric values, you can simply enter the numeric value (6) as a criteria argument.

Use the following formula.

Read More: SUMIFS Not Working with Multiple Criteria

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:

1.2 SUMIF with Text at Start

The formula will be:

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.

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.

2.2 SUMIF for Character Within Text

• Insert the formula below in cell E6.

• Press the ENTER key.

Alternatives for SUMIF Function with Tilde (~) Wildcard

• Insert the formula below in cell E5.

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

Download Practice Workbook

Related Articles

• Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria
• How to Use SUMIFS Function with Multiple Sheets in Excel
• Excel SUMIFS Not Equal to Multiple Criteria

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