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:

Using SUMIFS Function with wildcard to Fetch Text Contained Within a Text String in Excel


1.2 Text Starting with Certain Text

Steps:

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

using SUMIFS function to Fetch Text Starting with Certain Text in Excel

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.

Fetching Text Ending with Certain Text using SUMIFS with wildcard

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.

Read More: How to Use SUMIFS When Cells Are Not Equal to Multiple Text


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.

Utilizing SUMIFS Function with Question Mark Wildcard


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.

Utilizing SUMIFS Function with Question Mark Wildcard

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.
=SUMIFS(C5:C13,B5:B13,"B*~?")
  • Press ENTER.

Incorporating SUMIFS Function with Tilde Wildcard in Excel


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

Steps:

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

SUMIFS wildcard not working

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

fix of SUMIFS with wildcard not working in Excel

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:

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

SUMIF with Text Within


1.2 SUMIF with Text at Start

The formula will be:

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

SUMIF with Text at Start


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)

SUMIF with Text at End


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)

SUMIF for Unknown Length of Characters


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.

SUMIF for Character Within Text


Alternatives for SUMIF Function with Tilde (~) Wildcard

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

SUMIF Function with Tilde Wildcard

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.


Download Practice Workbook


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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo