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
Bookmarks