searching for words using countif

[bouthie]

Much appreciation to the geniuses out there.

I have a list of words i want to count in an array of phrases

I don't care about case sensitive but I do want only whole words

Sample list in column -A-
-A-
of
and
I
the
fun


sample array in columns -E- and -F-


-E---------------------------------F-
on top of the hill and fun----------I have fun
The cow is red --------------------sand


I have tried many variations of =COUNTIF(E2:F3,"*"&A3&"*") However this returns 2 for "sand" and "and"

I also tried

SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range,A2,"")))/LEN(A2))

How do I count only whole words? I hope this is clear and specific enough.

 

[DonkeyOte]

Welcome to the Board.

Perhaps the below will be of interest ?

Excel Workbook
	A	B	C	D	E	F
1	of	1			on top of the hill and fun	I have fun
2	and	1			The cow is red	sand
3	I	1
4	the	2
5	fun	2
Sheet3

 

[Colin Legg]

...deleted

 

[bouthie]

DonkeyOte,

You my friend are freakin' Awesome. Many thanks. Beverage on me if you ever come to Monterrey, Mexico.