#### ttowncorp

##### Board Regular
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>

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### CalcSux78

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

#### Gilles1

##### New Member
You could try using the countif formula.

=COUNTIF(column, "*searchword*")

The searchword has to be between the asterisks

Last edited:

#### jlhoover3

##### Board Regular
 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

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

#### Weazel

##### Well-known Member
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
ABCD
1moonthecow
2the cow jumped over the moon121
3but the cow and the cat had a milk moon pie121

<tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
B2=(LEN(\$A2)-LEN(SUBSTITUTE(\$A2,B\$1,"")))/LEN(B\$1)

<tbody>
</tbody>

<tbody>
</tbody>

#### ttowncorp

##### Board Regular
this works perfect. thank you

You could try using the countif formula.

=COUNTIF(column, "*searchword*")

The searchword has to be between the asterisks

#### FDibbins

##### Well-known Member
Your answer suggested that you wanted to do this all in 1 cell?

#### Gilles1

##### New Member
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

Last edited:

Replies
22
Views
418
Replies
1
Views
352
Replies
5
Views
180
Replies
7
Views
205
Replies
6
Views
468

1,191,025
Messages
5,984,201
Members
439,877
Latest member
kellylet

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back