Count the number of times a word appears in a range?

Posted by Marianne on December 31, 2001 10:48 AM

Hi there,

I am looking for a formula to count the number of times a word appears in a range. The problem that I am having is that the word is mixed in with other words in the cell (like in a sentence).

I want to find how many times the word "OR" (case sensitive)appears in cells B16:B4000.

The Countif formula only finds the results if the word "OR" is the only word in the cell.

Thanks,

Marianne

Posted by Scott on December 31, 2001 11:36 AM

You could try this:

=COUNTIF(B16:B4000,"*OR*")

However, because it counts cells, if a cell has more than one "OR" in it, it will only count it as one.

Posted by Marianne on December 31, 2001 12:03 PM

Good But... Can it be case sensitive?

Scott, Thanks so much. This calculation is headed in the right direction. Like you said, though, it only counts it once and there are cells with it more than once. If you can think of something else to count all of them that would be great.

Also, is there a way to make it case sensitive?

Thanks,

Marianne

Posted by Gary Bailey on December 31, 2001 12:35 PM

Re: Good But... Can it be case sensitive?

Try the following array formula

=(SUM(LEN(B16:B4000))-SUM(LEN(SUBSTITUTE(B16:B4000,"OR",))))/LEN("OR")

It should be case sensitive and count multiple instances in one cell.

Remember to Control+Shift+Enter it.

Gary

Posted by Marianne on December 31, 2001 1:30 PM

Wow, I would never have thought of that! It works great! Thanks. NTF

Posted by Aladin Akyurek on January 01, 2002 11:03 AM

Equivalently... (Re: Good But... Can it be case sensitive?)

the non-array formula

=SUMPRODUCT((LEN(B16:B4000)-LEN(SUBSTITUTE(B16:B4000,C1,"")))/LEN(C1))

where B16:B4000 is the search range and c1 houses the search string.

Aladin

=========