I want to count the number of times each entry in an array of text strings appears within the array. The tricky part is that I want to count not only exact matches but also strings contained within longer strings, so partial matches as with wildcards (*).
Take the following example:
AATTCCGG, =COUNTIF(A1:A3,"*AATTCCGG*")
TTCCGG, =COUNTIF(A1:A3,"*TTCCGG*")
ATCGATCG, =COUNTIF(A1:A3,"*ATCGATCG*")
This returns 1, 2, 1, which is correct (because the second line of data is a substring of the first). The problem is I want to substitute a cell address (e.g., A1) for the actual quoted text string (e.g., AATTCCGG) in the COUNTIF criteria without losing the ability to find a partial match.
Any ideas?
Thank you.
Bookmarks