My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel Formulas / Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP
Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP
by

Wildcards *?~....what am I talking about? And no, it's not an expletive. Let’s start with an example.

COUNTIF using Wildcards

Excel COUNTIF text in a string using wildcards

What say you wanted to count the number of cells containing the word ‘apple’ in this table.

You could simply use a wildcard (an asterisk, *, is a wildcard in Excel) in your COUNTIF formula like this:

=COUNTIF(A5:A9,"*apples*")

Your result will be 4.

Notice that the wildcard search is not case sensitive and it will count any instance of the word, even where it’s not a word on its own like in the case of ‘Pineapples’.

Alternatively, if you wanted to reference a cell instead of typing in the word ‘apples’ your formula would be:

=COUNTIF(A5:A9,"*"&A1&"*")

Where cell A1 contains the word ‘apples’.

All we are doing here is adding an asterisk to the word ‘apples’ in A1 using the ampersand. More on using the ampersand (&) to join text.

SUMIF using Wildcards

Excel SUMIF text in a string using wildcards

The SUMIF formula in cell B23 is:

=SUMIF(A17:A21,"*apples*",B17:B21)

VLOOKUP Formula using Wildcards

In the table below is a list of Car Manufacturers and their location. We’ve named the range of this table car_manufacturers.

Excel VLOOKUP tex in a string using wildcards

In column B of the table below we want to find the location of the car manufacturer but we only want to type in the short name for the manufacturer into Column A. e.g. 'Ford' instead of 'Ford Motor Company of Australia'.

Excel VLOOKUP tex in a string using wildcards

In cell B29 our VLOOKUP formula is:

=VLOOKUP("*"&A29&"*",car_manufacturers,2,FALSE)

Again, we’ve used the ampersand to add wildcards around ‘Ford’ in cell A29 before we look it up in our car_manufacturers table.

The Final Word on Wildcards

The other Wildcards you can use are the question mark ? and tilde ~.

The asterisk we've used already allows you to search for a string of text, but if you only want to search for one variable you can use the question mark wild card like this:

? Wildcard

Excel ? wildcard 

=COUNTIF(G10:G13,"apple?")

gives a result of 3.

Notice it will find words ending in 's' like 'apples' but won't find 'apple' because the ? is a place holder for another character.

~ Wildcard

Excel tilde wildcard 

=COUNTIF(A5:A9,"*~*apples*")

give a result of 1

The tilde wild card allows you to search for words that contain a wild card - either * or ?.

Simply placing the tilde before the asterisk tells Excel that the asterisk is not to be used as a wildcard.

There are other formulas you can use Wildcards with so feel free to experiment. Please post your discoveries in the comments below for all to share.

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.

If you liked this sign up for our Excel newsletter below and receive weekly tips & tricks like this straight to your inbox.

Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP

More Vlookup Posts

vlookup in power query using list functions
Use List functions to do VLOOKUPS in Power Query as an alternative to merging tables. Sample data and file available to download
VLOOKUP Multiple Values in Multiple Columns
Excel VLOOKUP Multiple Sheets
Excel Factor 17 Lookup and Return Multiple Matches
Excel Factor 16 Dynamic Lookup
VLOOKUP Multiple Criteria
Excel VLOOKUP Multiple Values
Excel VLOOKUP with Dynamic Column Reference
Excel VLOOKUP with Dynamic Column Reference allows you to copy the formula across columns and have the column reference automatically update.
Excel VLOOKUP to the Left Using CHOOSE
VLOOKUP to the left with this clever use of CHOOSE to trick Excel into looking up a column to the left.
hlookup function
How to use Excel HLOOKUP formula, including examples and workbook download.

More Excel Formulas Posts

ai-aided excel formula editor
Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts
Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment
Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips
Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays
The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do
Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function
The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files
New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel
How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions
TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Tag:

Reader Interactions

Comments

  4. Brendan Ellis

    Your solutions are very clever, please can you help with a formula to extract a registration number from mixed text in a cell that includes the rego somewhere. Here are a few examples, see list below:
    The rego number is always 3 numbers & 3 letters e.g. below 332TCF

    AV0386, 332TCF, 25Seater Bus, 0293, OH150325
    AV0151, Hilux, 639SIW, OFA, off Hired 04/12/14
    BQ93YO
    (094SLP) Toyota Hilux (Avis)
    (BS88ML) Toyota Hilux (avis)
    LV1319, LC UTE, 159TRP, Rural, B Josephs
    (368TFM) Toyota Prado
    (001TGU) Mazda BT50
    199VBE
    865SLJ Toyota Hilux Cab (HV1256)
    734TIU
    (814TIZ) Mazda BT50

    Thank you
    Brendan

    Reply

    • Catalin Bombea

      Hi Brendan,
      To do that, the easiest way is to use a function, using a regular expression pattern:
      Function RegCode(ByVal Text As String, Optional Pattn As String = "[0-9]{3}[a-zA-Z]{3}") As String
      Dim Result As String, i As Integer
      Dim allMatches As Object
      Dim re As Object
      Set re = CreateObject("vbscript.regexp")

      re.Pattern = Pattn
      re.Global = True
      re.IgnoreCase = False
      re.MultiLine = False
      Set allMatches = re.Execute(Text)

      For i = 0 To allMatches.Count - 1
      Result = Result & "," & allMatches.Item(i)
      Next

      If Len(Result) <> 0 Then
      Result = Right(Result, Len(Result) - 1)
      End If

      RegCode = Result

      End Function

      Just add the above function in a vba module, and all you need to do next is to use this new function in worksheet cells, like:
      =RegCode(A1) (the pattern is optional, by default the 3 digits and 3 alpha chars will be used: “[0-9]{3}[a-zA-Z]{3}” (it’s written inside the function)
      You can also specify other patterns, if you like:
      =RegCode(A1,"\(([a-zA-Z]{0,9})([0-9]{0,5})([a-zA-Z]{0,9})\)") I won’t tell you what this pattern does, you will have to find that yourself 🙂
      Cheers,
      Catalin

      Reply

  6. Wanda Ponto

    Lets say I wanted to count the number of cells containing the word ‘apple’ in your table… a red apple, gala apple, macintosh apple as those are “apples”. I don’t want a pineapple because it isn’t an apple – its a pineapple. What formula would you use then?

    Reply

    • Catalin Bombea

      Hi Wanda,
      The first step to build a formula, is to analyze the data. Identifying patterns in data is the most important in this case. In this case, i will use this pattern: “the word apple has a space in front of it, if ‘apple’ is contained in another word, don’t count it”. For these 4 values: “red apple” , “macintosh apple” , ” gala apple”, ” pineapple”, the function: =COUNTIF(A12:A15,”* apple*”) will return 3, because “pineapple” does not correspond to the pattern. The “*” wildcard used in criteria will tell the function that there can be any characters in front of and after the sequence ” apple”.
      Hope it’s clear 🙂
      Catalin

      Reply

  7. Brad Morse

    Wild cards don’t work in array formulas or with SUMPRODUCT:

    =SUMPRODUCT((C3:C10)*(B3:B10″Invoice $”)*(B3:B10″Ck #483″))
    or
    {=SUM((C3:C10)*(B3:B10″Invoice $”)*(B3:B10″Ck #483″))}

    I wanted to use “Invoice*” and “Ck*” but the “wildcard” asterisk is ignored.

    Reply

    • Mynda Treacy

      Hi Brad,

      You can use this formula:

      =SUMPRODUCT(C3:C10*(ISNUMBER(SEARCH("ck*",B3:B10))+(ISNUMBER(SEARCH("Invoice $*",B3:B10)))))

      Array entered i.e. CTRL+SHIFT+ENTER

      Kind regards,

      Mynda.

      Reply

  8. Angelique

    Mynda,
    I can’t find anything that states this outright but it seems that the wildcards will not include empty strings. So if I do a SUMIFS statement and the values for one of the conditions are NULL or a blank string, it will not include those corresponding values in the sum. Is there a work around that you know of?

    Example referencing your SUMIF statement above: if you change the formula to =SUMIF(A17:A21,”*”,B17:B21) and delete the “green apples” in A18, the result is $241.00.

    Reply

  10. Michael Bugg

    Thank you very much for your website; it’s wonderful finding _clear_ answers to my Excel questions.

    Reply

  13. bob

    Your vLookup wildcard trick does not work is Column A’s keyword has multiple keywords in it. For example instead of just having “ford” if it has “ford motor” it error’s out.

    Any solution for that bug?

    Reply

    • Mynda Treacy

      Hi Bob,

      It’s not a bug, the formula is doing what it is supposed to do. i.e. Find a specific word within a cell. It actually does work for ‘Ford Motor’ but it would give an error if you looked up ‘Ford Car’, since the string ‘Ford Car’ does not exist in the Lookup Table.

      Are you saying you want to look up ‘Ford Car’ and if it finds ‘Ford’ or ‘Car’ it should give you the result? Can you please be more specific about what you want your formula to do.

      Thanks,

      Mynda.

      Reply

      • Scott

        I have a request similar to bob’s – I believe. I want a Lookup table that contains wildcards (e.g. *Ford*, *Audi*) and then if the string being looked up contains a match, return the value in the second column. For example, I want “Ford Car” and “Henry Ford” to return Geelong, Victoria.

        Is this possible? Any assistance would be fantastic!

        Regards,
        Scott

        Reply

        • Carlo Estopia

          Hi Scott,

          I have downloaded the workbook and it works just fine.

          I even tried to use ‘Motor’ as the look-up value and it still work.
          I’m referring to this formula:

          =VLOOKUP("*"&A29&"*",car_manufacturers,2,FALSE)

          and this table

          Car Manufacturer	         Location
Ford Motor Company of Australia	 Geelong, Victoria
General Motors-Holden Ltd	 Port Melbourne, Victoria
Audi Australia	                 Botany, NSW
BMW Group Australia	         Mulgrave, VIC
Chrysler	                 Dandenong, VIC

          Cheers,

          CarloE

          Reply

  14. sathi kunti

    please show me how excel formula and function is used in banking back office job. and data entry job with example

    Reply

    • Mynda Treacy

      Hi Sathi,

      There isn’t one, or even a bunch of formulas that are specific to ‘banking back office jobs’. I know because I worked in the back office of investment banks for 8 years. For this type of job you’d need to know Excel to an advanced level.

      You can find an index of tutorials on common formulas here.

      I hope this points you in the right direction.

      All the best,

      Mynda.

      Reply

  15. Sue-Ting Chene

    For COUNTIF using WILDCARDS, I needed a “&” before AND after the A1

    so instead of
    =COUNTIF(A5:A9,”*”&A1”*”) Where cell A1 contains the word ‘apples’.

    use
    =COUNTIF(A5:A9,”*”&A1&”*”) Where cell A1 contains the word ‘apples’.

    This particular use was hard for me to find online. Thanks!

    Reply

    • Mynda Treacy

      Hi Sue-Ting,

      Yes, correct. You need an ampersand before and after the A1 reference. I will correct the typo in the post above. Sorry for the confusion.

      Kind regards,

      Mynda.

      Reply

  16. David M

    Mynda,
    Your site was the only site I found that demonstrated how to do a HLOOKUP wildcard search linked to a cell reference (“*”&K3&”*”). Most have the actual value hardcoded in the formula which wasn’t practical for my purpose.
    Thank you for solving my problem.

    Reply

  17. Laura

    I am trying to use a wildcard in my VLOOKUP formula that will find values in column A that contains a value from K3 (an approximate match). If it finds K3 then I want it to sum up columns 3 through 9. Here’s what I have, which is giving me a result of 0. It should give me $65k.

    =SUM(VLOOKUP(“*”&K3&”*”,A:I,{3,4,5,6,7,8,9},FALSE))

    K3 = Health Care
    Column A has “Health Care – Facility B”

    Reply

    • Mynda Treacy

      Hi Laura,

      Try typing the double quotes in Excel again. Notice how the double quotes in your formula are on a slant? When I copied your formula into Excel it didn’t work either. Then I typed the double quotes in again and voila, all good.

      Oh, and this is an array formula so you’re entering it with CTRL+SHIFT+ENTER aren’t you?

      I hope that fixes it at your end too. Let me know if not.

      Kind regards,

      Mynda.

      Reply

Trackbacks

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *