counting instances of the same word (excel 2000)

**nickb** · 2003-10-22 10:22

I want to count cells with the same word in them, can anybody help me?
Many thanks in advance Nick
<img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

**PrestonK** · 2003-10-22 10:32

Use CountIf(A1:A500,"word") to find how many times word occurs in whatever range you specify.

**nickb** · 2003-10-22 10:36

thank you Preston

**johnr** · 2003-10-22 19:17

You might want to refine the test string to something like
[space]word[space]
to ensure that you were getting only instances of the word
'word'
and not variants like password, wordsmith or swordsmith for that matter.
What happens if the word you want is the last word in the cell and so has no following space (a full stop, a question mark, maybe?) is a good way of prolonging this thread!
Similar considerations can apply if you use autofilter to find instances of a word in a column.
Best of luck!

**BilasTasol** · 2003-10-23 10:24

John:

I don't follow your reasoning. Surely =COUNTIF(A1:A500,"word") will count only those cells which contain word and nothing else.

Tony.

**sdckapr** · 2003-10-23 10:36

You are correct, but "contain" is the wrong term (which relateds to John's suggestion)

=COUNTIF(A1:A500,"word")

Will only count the times that a cell in A1:A500= "word"

It will not count "sword", "words", "wordsmith", " word", or "word " or any other word which contains "word", it will only find those that equal "word" exactly

It is, however, NOT case-sensitive: It will find WORD, Word, wOrd, and other variants.

Steve

**johnr** · 2003-10-23 11:01

You're right, Basil(?)
I was confused by the idea of 'Find', which does find all instances of the string
word
in the list I offered.
To make the countif formula find all the variants I mentioned, it would need to be wildcarded, eg =countif (list, "*word*).
As if anyone would want to...

**BilasTasol** · 2003-10-23 11:13

Steve:

That's why I said 'cells which contain word and nothing else' - i.e. each cell counted contains nothing else but word. Sorry if my reply was ambiguous.

Tony.

**BilasTasol** · 2003-10-23 11:16

John:

Are you confusing me with a certain lunatic hotel manager?

Tony.

**sdckapr** · 2003-10-23 11:58

I thought you were correct, I was just rrying to be even less ambiguous (I get anal sometimes) to make it clear. "contains" is used for references like sword, words etc when using filter as opposed to "=" and I just wanted to be very clear since John had raised the question.

If the "basil" you refer to is "fawlty" I would not say he is a lunatic, just a "little" high-strung! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Steve

**HansV** · 2003-10-23 12:04

Just don't mention the war! <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

**pieterse** · 2003-10-23 12:09

I'm from Barcelona, I know nooothing.

**BilasTasol** · 2003-10-23 13:38

Jan, if you know nothing, there's no hope for me!

Incidentally, isn't it interesting how, with one sidelong mention of F.T., the WMVPs come out of the woodwork! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

Tony.

**sdckapr** · 2003-10-23 14:12

Just NEVER mention the 2(!) American versions of this (they both stunk)!

Steve

**johnr** · 2003-10-23 21:42

John wrote
>>is a good way of prolonging this thread!

Looks like my first success this week.
Over and out.

Thread: counting instances of the same word (excel 2000)

Thread Tools