adding up words

[ttowncorp]

is there a way to add up words in a sentence in excel? for example in A1 I have "the cow jumped over the moon" and in cell A2 I have " but the cow and the cat had a milk moon pie". and I wanted to add up how many times the word "cow" or "moon" are typed in my sentence's.

	moon	the	cow
the cow jumped over the moon	2	4	2
but the cow and the cat had a milk moon pie

<tbody>
</tbody>

 

[CalcSux78]

Not sure if you're familiar with vba dictionaries, but seems like the perfect function for your dilemma.

 

[Gilles1]

You could try using the countif formula.

=COUNTIF(column, "*searchword*")


The searchword has to be between the asterisks

 

[L. Howard]

Take a look here.

Howard


https://exceljet.net/formula/count-specific-words-in-a-range

 

[jlhoover3]

Column A	Column B	Column C	Column D
the cow jumped over the moon	moon	the	cow
but the cow and the cat had a milk moon pie	2	4	2

<tbody>
</tbody>

=SUMPRODUCT((LEN($A$1:$A$2)-LEN(SUBSTITUTE($A$1:$A$2,C1,"")))/LEN(C1))
=SUMPRODUCT((LEN($A$1:$A$2)-LEN(SUBSTITUTE($A$1:$A$2,D1,"")))/LEN(D1))
=SUMPRODUCT((LEN($A$1:$A$2)-LEN(SUBSTITUTE($A$1:$A$2,E1,"")))/LEN(E1))

 

[FDibbins]

Try this...
=SUMPRODUCT(LEN($A$2:$A$3)-LEN(SUBSTITUTE($A$2:$A$3,B$1,"")))/LEN(B$1)
copied across

 

[Weazel]

I'm not following why you're putting the range into the substitute if you're only evaluating 1 row at a time.

unless I'm missing something

Excel 2013

	A	B	C	D
1		moon	the	cow
2	the cow jumped over the moon	1	2	1
3	but the cow and the cat had a milk moon pie	1	2	1

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula B2 =(LEN($A2)-LEN(SUBSTITUTE($A2,B$1,"")))/LEN(B$1) <tbody> </tbody>

<tbody>
</tbody>

 

[ttowncorp]

this works perfect. thank you

You could try using the countif formula.

=COUNTIF(column, "*searchword*")


The searchword has to be between the asterisks

 

[FDibbins]

Your answer suggested that you wanted to do this all in 1 cell?

 

[Gilles1]

I tried it myself and sadly it doesn't work when the same word is in the cell twice (this formula only counts the cells).

But FDibbins came up with the solution that works well :

=SUMPRODUCT((LEN(A:A)-LEN(SUBSTITUTE(UPPER(A:A),UPPER(B1),"")))/LEN(B1))


The upper in the formula accounts for the difference in upper and lower case entries of the same word

this works perfect. thank you