I can't quite figure out what exactly you're looking to count, but...here are some guesses (using your posted workbook)
This regular formula returns the count of Col_A cells that contain the Col_D value
E2: =COUNTIF($A$1:$A$7,"*"&D2&"*")
This regular formula returns the number of times the Col_D value occurs in Col_A (assuming some cells may contain repeated values..."White Ponys are white ponys" would count as 2 occurrences
F2: =SUMPRODUCT((LEN($A$1:$A$7)-LEN(SUBSTITUTE(UPPER($A$1:$A$7),UPPER(D2),"")))/LEN(D2))
Using the sample data,
E2 returns: 3
F2 returns 3
Is that something you can work with?
Bookmarks