Word count

**lachandl** · 12-18-2007, 02:29 PM

Is there a formula that can count the times a certain word is in the column?

**StephenR** · 12-18-2007, 02:41 PM

=COUNTIF(A1:A10,"word")

or

=COUNTIF(A1:A10,"*word*")

if you want wildcards.

**lachandl** · 12-18-2007, 02:47 PM

I should of been more clear.
I need it put the word in cell with the number
Like 9 No's, 11 Yes

**Ron Coderre** · 12-18-2007, 02:47 PM

With
A1: (the word to count....eg Sugar)

If you only want  to the cells if the word is the only contents of the cell,
D1: Sugar
D2: Sugar and Spice.
D3: Sugar

try something like this:
A2: =COUNTIF(D1:D10,A1)

Count of Sugar is 2.
----------------------

OR...if the word will occur only once, but may be embeded in other text
like this....
D1: Sugar
D2: Sugar and Spice.
D3: Sugar

try something like this:
A2: =COUNTIF(D1:D10,"*"&A1&"*")

Count of Sugar is 3.
----------------------

BUT...If the word may be embedded in other text, 
and possibly repeated
like this....
D1: Sugar and Spice
D2: Sugar Bears contain no Sugar.
D3: Spice

Then this formula counts each occurrence:
A2: =SUMPRODUCT(LEN(D1:D10)-LEN(SUBSTITUTE(UPPER(D1:D10),UPPER(A1),"")))/LEN(A1)

Count of Sugar is 3.

Note: SUBSTITUTE is case sensitive, hence the UPPER function.