Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    counting instances of the same word (excel 2000)

    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>

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  3. #3
    New Lounger
    Join Date
    Mar 2001
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    thank you Preston

  4. #4
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  5. #5
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    John:

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

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  7. #7
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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...

  8. #8
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  9. #9
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    John:

    Are you confusing me with a certain lunatic hotel manager?

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    I'm from Barcelona, I know nooothing.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

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

    Steve

  15. #15
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •