+ Reply to Thread
Results 1 to 3 of 3

is it possible to use countif with a cell ref and wildcards?

  1. #1
    Registered User
    Join Date
    08-25-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    1

    is it possible to use countif with a cell ref and wildcards?

    Is it possible to count the number of times a value appears in a range using a cell ref as the criteria, but the value will only be part of a string in the cells in the range?

    in other words:

    range values:
    A , B
    "1 v 2" , "11 v 1"
    "3 v 4" , "12 v 3"
    "1 v 3" , "1 v 13"
    "2 v 4" , "12 v 11"

    I want to count how many times "1" appears in the range? And 2, and 3, etc... so I can generate a list like this:

    C,D
    1,4
    2,2
    3,3
    4,2
    11,2
    12,2
    13,1

    Experimenting, I came up with a very non-elegant solution that kinda works, but I'm hoping there's a better way:

    C,D
    1,=countif($A$1:$B$4,"1 v*")+countif($A$1:$B$4,"*v 1")
    2,=countif($A$1:$B$4,"2 v*")+countif($A$1:$B$4,"*v 2")

    and so on...

    I'd prefer to have something more like:

    1 =countif(<range>,"<the part I can't figure out with wildcards>" & C1)

    in other words, how do I say, "count if the value of my reference cell is contained within the text of a cell in the range" ??

    I have both 2003 and 2007 available. Not sure if this is possible, but I'd appreciate the help!

    TIA

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: is it possible to use countif with a cell ref and wildcards?

    If you're looking to opt in favour of elegance over efficiency then SUMPRODUCT may be more useful, eg:

    Please Login or Register  to view this content.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: is it possible to use countif with a cell ref and wildcards?

    I think you may need 2 COUNTIFs but you can add the cell reference like this

    =COUNTIF($A$1:$B$4,C1&" v*")+COUNTIF($A$1:$B$4,"*v "&C1)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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