Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35
Like Tree2Likes

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

This is a discussion on Need to count number of occurrences of a word in a column within the Excel Questions forums, part of the Question Forums category; Column A has 100 entries (A2 through A101) Some of these entries are "Houston" What formula can I use to ...

  1. #1
    Board Regular
    Join Date
    Nov 2002
    Location
    New Orleans
    Posts
    62

    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

    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

    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

    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

    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
    74,205

    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

    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,403

    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".
    Khornight likes this.
    Using Excel 2007 (Dutch) on Windows 7 and Excel 2013 (Dutch/English) on Windows 8.1.
    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
    74,205

    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

    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.

Page 1 of 4 123 ... LastLast

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