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

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???

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



--
Regards,
Tom Ogilvy






"sizz1@hotmail.com" wrote:

> 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???
>
>

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

Originally Posted by sizz1@hotmail.com

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???

Thanks

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

Again thank you very much!

Yes i tried this and it works too!!

Thanks again guys!