Skip to content

Using Wildcards * & ? in Excel SUMIFS and COUNTIFS Functions

    Using Wildcards * & ? in Excel SUMIFS and COUNTIFS Functions

    This tutorial explores the use of the wildcard characters * and ?, both of which can be used within the criteria arguments of the COUNTIFS and SUMIFS function.  The tutorial assumes you have a working knowledge of both functions. Download the featured file here.

    The asterisk character (*) can be used before, after and surrounding criteria to allow partial search criteria to be used. The question mark character (?) is used to represent the position within the criteria where any character is allowed – please see examples below:

    WER* Starts with WER
    *WER Ends with WER
    *WER* Contains WER
    W?R Any character allowed in the ? position

    Working with * Wildcards

    Here’s our scenario.

    Scenario

    We need to use a COUNTIFS function to count the number of product codes that belong to these individual product categories…

    GHY
    POT
    KUY
    CVR
    ASD
    GTY

    …where the quantity is greater than 350 and excluding products that display –T at the end of their product code (see sample data below).

    product codes

    Our first criteria will exclude product codes that end with –T. Our criteriarange1 is $a$2:$a$0 which is an absolute reference for our list of product codes.  Our criteria1 counts everything but those codes ending with –T: the criteria is “<>*-T”.

    <>  not equal to

    *-T  ends with –T

    Note the criteria has to be enclosed in double speech marks including the comparison operator <>

    countifs using ends with wildcard criteria

    Our second criteria specifies the product category we want to count on. The product categories have been listed in column E.  We are looking for product codes that start with these three characters.

    Criteriarange2 is $A$2:$A:$20, and criteria2 is E2&“*”

    E2 being the cell containing the product category

    & concatenates (joins) the value in the cell reference with the wildcard character

    “*” our wildcard character in double quotation marks.

    The result of this concatenation would be GHY* – anything that starts with GHY.

    countifs using starts with wildcard criteria

    Our third criteria simply specifies that we only want to count product codes where the quantity is greater than 350. No wildcards are needed for this criteria.

    Criteriarange3 is $B$2:$B$20 (our quantity column). Criteria3 is “>350”

    Note the criteria is surrounded by double quotation marks.

    countifs wildcard criteria

    Here are our results…

    Scenario2
    In column G we need to calculate the total quantity for products ending with  -T within each product category.

    The formula for this would be (I’ve emboldened the criteria).

    sumifs using wildcard criteria

    =SUMIFS($B$2:$B$20,$A$2:$A$20,“*-T”,$A$2:$A$20,E2&”*”)

    “*-T” specifies product ending with –T and E2&”*” specifies product code starting with the value held in cell E2.

    Here are the formula results.

    results

    Using ? Wildcards

    In this scenario we need to count the number of product codes that contain the character pattern P?T anywhere in their product code. ? being a placeholder for any character.  Only products where the quantity is greater than 3500 are to be included.

    scenario3

    The formula for this calculation would be…

    =COUNTIFS(A2:A20,“*P?T*”,B2:B20,“>3500”)

    The criteria has been emboldened.

    Our criteria1 is “*P?T*”. Note the two asterisks, either side of the criteria – this specifies that the P?T pattern can exist anywhere in the product code. P?T specifies that pattern we are looking for is P followed by any character followed by T.  The whole criteria must be surrounded by double quotation marks.

    Here’s our result

    result2

    Leave a Reply