• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP

You are here: Home / Excel Formulas / Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP
Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP
August 31, 2011 by Mynda Treacy

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.
Please enter a valid email address.

Download the workbook and have a play around. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

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
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Vlookup Posts

vlookup in power query using list functions

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

VLOOKUP Multiple Values in Multiple Columns

Excel VLOOKUP Multiple Sheets

Excel VLOOKUP Multiple Sheets

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

VLOOKUP Multiple Criteria

VLOOKUP Multiple Criteria

Excel VLOOKUP Multiple Values

Excel VLOOKUP Multiple Values

Excel VLOOKUP with Dynamic Column Reference

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

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

Excel HLOOKUP Formulas Explained

How to use Excel HLOOKUP formula, including examples and workbook download.

More Excel Formulas Posts

Summarize Months to Quarters

Excel Formulas to Summarise Monthly Data into Quarters

3 ways (good, better, best) to summarize monthly data into quarters using formulas. Lots of examples and sample file to download.
Excel BYROW and BYCOL Functions

Excel BYCOL and BYROW Functions

Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows.
python in excel natively

How to Use Python in Excel Natively

How to use Python in Excel natively using libraries like Pandas, NumPy, Matplotlib, Seaborn and more for analysis and spectacular charts!
excel dynamic named ranges

Excel Dynamic Named Ranges

Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas and PivotTables etc.
functions for financial modelling

Excel Functions for Financial Modeling

Top 23 must know Excel functions for Financial Modeling. Includes example Excel file and step by step instructions.
excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

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

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 Labs (Formerly, Advanced Formula Environment)

Excel Labs is a long awaited, new improved way to write, name and store Excel formulas, including LAMBDAS with the help of AI.
Pro Excel Formula Writing Tips

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.


Category: Excel FormulasTag: vlookup
Previous Post:Excel SEARCH and You Will FINDExcel SEARCH and You Will FIND
Next Post:Easy Excel Database FunctionsEasy Excel Database Functions

Reader Interactions

Comments

  1. David

    October 12, 2019 at 6:57 pm

    Very many thanks for your generosity in training us to be best in the use of Excel at zero cost.

    Reply
  2. Dan S

    September 25, 2018 at 5:30 am

    This is awesome, thanks.

    Reply
    • Mynda Treacy

      September 25, 2018 at 9:15 am

      You’re welcome, Dan! Glad we can help.

      Reply
  3. justine dougherty

    September 16, 2016 at 11:20 pm

    Mynda,
    Is there a way to print all of these without having to click – thanks!

    Reply
    • Philip Treacy

      September 18, 2016 at 10:54 am

      Hi Justine,

      I’m not clear on what you are trying to print?

      Regards

      Phil

      Reply
  4. Brendan Ellis

    May 28, 2015 at 5:19 pm

    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

      May 28, 2015 at 9:37 pm

      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
  5. Rizwan Ansari

    September 25, 2014 at 6:15 pm

    PPPPPPPPP = 9 whose formula using

    Reply
    • Mynda Treacy

      September 25, 2014 at 6:38 pm

      Hi Rizwan,

      Sorry, I don’t know what you mean.

      Mynda

      Reply
  6. Wanda Ponto

    January 26, 2014 at 11:00 am

    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

      January 26, 2014 at 3:42 pm

      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

    October 9, 2013 at 4:53 pm

    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

      October 9, 2013 at 9:21 pm

      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

    October 1, 2013 at 4:10 am

    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
    • Mynda Treacy

      October 1, 2013 at 7:25 pm

      Hi Angelique

      You can use this SUMPRODUCT formula:

      =SUMPRODUCT(B17:B21,--(A17:A21=""))

      Kind regards,

      Mynda.

      Reply
  9. mostafa ahmadi

    September 28, 2013 at 8:45 pm

    from iran
    thanks of help

    Reply
    • Mynda Treacy

      September 29, 2013 at 9:24 pm

      You’re welcome 🙂

      Reply
  10. Michael Bugg

    June 28, 2013 at 6:34 am

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

    Reply
    • Mynda Treacy

      June 28, 2013 at 10:36 am

      You’re welcome, Michael 🙂

      Reply
  11. Subash

    February 18, 2013 at 7:21 pm

    Just for your information:

    This formula works beautifully with sumif also.
    =SUMIF(A5:A9,”*apples*”)

    Rgds,
    Subash

    Reply
    • Carlo Estopia

      February 18, 2013 at 8:26 pm

      Hi Subash,

      Thanks for sharing.

      Carloe

      ps: I got your file already from HELP DESK. I’ll send it to you ASAP.

      Reply
  12. Jane

    February 15, 2013 at 10:45 pm

    Just wanted to say thank you. Your website was very helpful

    Reply
    • Carlo Estopia

      February 16, 2013 at 2:16 pm

      Hi Jane,

      On behalf of Mynda and Philip, You’re welcome.

      Cheers.

      CarloE

      Reply
  13. bob

    October 12, 2012 at 4:26 pm

    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

      October 14, 2012 at 9:14 pm

      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

        April 30, 2013 at 12:37 am

        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

          April 30, 2013 at 10:28 am

          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

    July 25, 2012 at 8:36 pm

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

    Reply
    • Mynda Treacy

      July 25, 2012 at 8:48 pm

      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

    July 19, 2012 at 2:56 pm

    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

      July 19, 2012 at 9:40 pm

      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

    July 10, 2012 at 9:37 pm

    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
    • Mynda Treacy

      July 10, 2012 at 9:44 pm

      🙂 thanks, David. Glad to help out.

      Reply
  17. Laura

    March 9, 2012 at 4:26 am

    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

      March 9, 2012 at 12:59 pm

      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

  1. Excel Wildcard Quirk Solved • My Online Training Hub says:
    September 14, 2014 at 9:40 am

    […] playing around with different formulas I noticed that wildcards were not behaving as I would […]

    Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x