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

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. ## 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. ## 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)

##### Users Browsing this Thread

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

#### 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