Need to count number of occurrences of a word in a column

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

Thread: Need to count number of occurrences of a word in a column

  1. #1
    Board Regular
    Join Date
    Nov 2002
    Location
    New Orleans
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need to count number of occurrences of a word in a column

     
    Column A has 100 entries (A2 through A101)
    Some of these entries are "Houston"

    What formula can I use to calc. how many times the word "Houston" appears within that Column so that I don't have to manually count each time???

    Thanks for any help.
    jase.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =COUNTIF(A1:A100,"Houston")
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Nov 2002
    Location
    New Orleans
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Geezum's -- you'd think I would have gotten that. I was close. Thanks a million.

    jase.

  4. #4
    New Member
    Join Date
    Oct 2009
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to count number of occurrences of a word in a column

    I am also need to count the number of occurences of a particular word across various cells and columns in Excel, however in my case cells don't contain a single word but a large amount of text.


  5. #5
    New Member
    Join Date
    Apr 2011
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to count number of occurrences of a word in a column

    Quote Originally Posted by Juan Pablo González View Post
    =COUNTIF(A1:A100,"Houston")
    Cool It really helped.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,444
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to count number of occurrences of a word in a column

    Quote Originally Posted by Cherique View Post
    I am also need to count the number of occurences of a particular word across various cells and columns in Excel, however in my case cells don't contain a single word but a large amount of text.

    Try...

    H2: manhattan

    I2:

    =COUNTIF(A1:F100,"*"&H2&"*")
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    Nov 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to count number of occurrences of a word in a column

    Quote Originally Posted by Cherique View Post
    I am also need to count the number of occurences of a particular word across various cells and columns in Excel, however in my case cells don't contain a single word but a large amount of text.

    Similarly I have a bunch of text in many cells of a sheet and want to count occurrences of words, but if a cell contains "with" three times I want the countrer for with to go up by three, countif will only count one per cell?

    Thanks

  8. #8
    Board Regular MarcelBeug's Avatar
    Join Date
    Apr 2014
    Posts
    1,811
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to count number of occurrences of a word in a column

    Try array formula, confirm with Ctrl+Shift+Enter:
    Code:
    =(SUM(LEN(A1:F100))-SUM(LEN(SUBSTITUTE(SUBSTITUTE(A1:F100,"with",""),"With",""))))/LEN("with")
    As this is case sensitive, I included both "with" and "With".
    Specializing in Power Query (M).
    Using Excel 2007 (Dutch) and Excel 2016 (Office 365 ProPlus) (Dutch/English), both on Windows 10.
    Array formulas can be recognised by the {} Excel puts around it automatically when you press Ctrl+Shift+Enter upon formula entry.

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,444
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to count number of occurrences of a word in a column

    Quote Originally Posted by Khornight View Post
    Similarly I have a bunch of text in many cells of a sheet and want to count occurrences of words, but if a cell contains "with" three times I want the countrer for with to go up by three, countif will only count one per cell?

    Thanks
    This excludes bits like without when looking for with...

    =SUMPRODUCT(LEN(" "&A1:F100&" ")-LEN(SUBSTITUTE(UPPER(" "&A1:F100&" ")," WITH ","")))/LEN(" with ")
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    New Member
    Join Date
    Nov 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to count number of occurrences of a word in a column

      
    Quote Originally Posted by MarcelBeug View Post
    Try array formula, confirm with Ctrl+Shift+Enter:
    Code:
    =(SUM(LEN(A1:F100))-SUM(LEN(SUBSTITUTE(SUBSTITUTE(A1:F100,"with",""),"With",""))))/LEN("with")
    As this is case sensitive, I included both "with" and "With".
    Thanks Marcel, that's perfect! Thanks

    Interesting idea Aladin, but it will fail to pick up words followed by comma's and other punctuation and for this it's easier to minus the count of withouts from the withs... (as it is I'm picking out certain words and I'm happy for Repair to find repairs and repaired...)

    Thanks for the help both.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com