+ Reply to Thread
Results 1 to 8 of 8

COUNT with wildcards

  1. #1
    Registered User
    Join Date
    05-17-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    5

    COUNT with wildcards

    I'm trying to return the number off cells that contain any variation of 23.2 as follows but can't get the correct values to return;

    A
    1 23.2
    2 23.2
    3 23.2 one
    4 23.2, one
    5 23.3

    I've tried COUNT(A1:A5,"23.2*") and variations of these using COUNTA, COUNTIF, no wildcards, wildcards *, ? etc but none will return the value I want, in this example it should be 4.

    It seems that it should be pretty simple but perhaps the mixture of no's and text is tripping it up or perhaps I'm just using the wrong formula.

    Can anyone help please?

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,037

    Re: COUNT with wildcards

    This works

    =SUMPRODUCT(--(ISNUMBER(SEARCH(23.2,A1:A5))))

    another solution here

    https://exceljet.net/formula/partial...-with-wildcard
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    05-17-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    5

    Re: COUNT with wildcards

    That works perfectly, many thanks.

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,892

    Re: COUNT with wildcards

    Try with
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  5. #5
    Registered User
    Join Date
    05-17-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    5

    Re: COUNT with wildcards

    I've hit another snag, is there a way of combining this with other data, so if I want to return the number of items that are both 23.2 and Done, e.g.;
    A. B
    1 23.2 Done
    2 23.2 Not started
    3 23.2 one In progress
    4 23.2, one Done
    5 23.3 Done

    In this example I'd expect to return a value of 2.

  6. #6
    Registered User
    Join Date
    05-17-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    5

    Re: COUNT with wildcards

    Thanks,for problem 1 I had tried =COUNTIF($A$1:$A$5,"*23.2*")that but it doesn't pick up the fields with both 23.2 and text.
    Last edited by M.G; 05-17-2017 at 07:51 AM.

  7. #7
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,037

    Re: COUNT with wildcards

    Quote Originally Posted by M.G View Post
    I've hit another snag, is there a way of combining this with other data, so if I want to return the number of items that are both 23.2 and Done, e.g.;
    A. B
    1 23.2 Done
    2 23.2 Not started
    3 23.2 one In progress
    4 23.2, one Done
    5 23.3 Done

    In this example I'd expect to return a value of 2.
    =SUMPRODUCT(--(ISNUMBER(SEARCH(23.2,A1:A5)))*(B1:B5="Done"))

    As you've added the "Done" parameter you may be able to remove the "--"

    =SUMPRODUCT((ISNUMBER(SEARCH(23.2,A1:A5)))*(B1:B5="Done"))

  8. #8
    Registered User
    Join Date
    05-17-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    5

    Re: COUNT with wildcards

    That's it, brilliant, thanks so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Wildcards in VBA
    By oodam in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-30-2013, 06:44 AM
  2. VBA & Wildcards
    By glindell in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-25-2013, 05:34 PM
  3. [SOLVED] If/And and Wildcards
    By consulttk in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-05-2012, 11:29 AM
  4. wildcards
    By Trachr in forum Excel General
    Replies: 3
    Last Post: 08-24-2012, 08:38 PM
  5. Wildcards
    By TechTed in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-27-2011, 06:47 PM
  6. Wildcards?
    By mycon73 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-19-2010, 10:52 AM
  7. wildcards in vba
    By shellshock in forum Excel General
    Replies: 3
    Last Post: 07-21-2005, 02:37 PM
  8. VBA Wildcards - HELP!
    By Co-op Bank in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-30-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1