Count 2 (Two) specific words in a cell in a given range

[sizz1]

Hi

Im currently having difficulty creating a formula which calculates 2
words in a cell in a given range

currently i have a formula which calculates 1 word in a cell

=COUNTIF('New Excel Sheet'!A9:Y28,"*WORD1*")

Works great when only counting 1 word.

The cells look like this ---- words which need counting are "Word1" &
"Word2"

A B C
-------------------------------------------------------
Test : Word1 : Word1
Test : Word2 : Word17
Word1 : :
---------------------------------------------------------
Word1 : Word1 : Word100
Word2 : Word2 : Test
Word17 : Word20 : Test
-------------------------------------------------------------

From this you can see that Word1 & Word2 appeared together in 3 cells

Please can anyone help me???

 

[Guest]

=COUNTIF('New Excel Sheet'!A9:Y28,"*WORD1*WORD2*")+COUNTIF('New Excel
Sheet'!A9:Y28,"*WORD2*WORD1*")



--
Regards,
Tom Ogilvy

 

[Ikaabod]

Would this work for your needs?
=COUNTIF('New Excel Sheet'!A9:Y28,"*WORD1*" & "*Word2*")+COUNTIF('New
Excel Sheet'!A9:Y28,"*WORD2*" & "*Word1*")

 

[sizz1]

Thanks

It works like a treat!!!!!!!!!!!!!!

Again thank you very much!

 

[sizz1]

Yes i tried this and it works too!!

Thanks again guys!