Counting instances of the word "January" in cells B12:B80
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Counting instances of the word "January" in cells B12:B80

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok anyone know how to count the number of instances the word "January" appears in the cell range B12:B80?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Georgia USA
    Posts
    569
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =COUNTIF(B12:B80,"January")

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Works great, thx =)

    wow, i've posted two things on this board now, both times i got the corrrect reply within 10 mins or so =)

  4. #4
    Guest

    Default

    On 2002-02-19 10:36, Paul B wrote:
    =COUNTIF(B12:B80,"January")
    what if the cell only contains the word january in the middle of the text e.g "one january morning" and you want to count the number of occurances of cells containing the word january?

  5. #5
    Guest

    Default

    answered it my self. A classic example of ask the question before engaging brain *january* - the power of widcards (note as i type it is 3pm on a friday, not classic thinking time.

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

    Default

    On 2002-02-28 17:35, Anonymous wrote:
    On 2002-02-19 10:36, Paul B wrote:
    =COUNTIF(B12:B80,"January")
    what if the cell only contains the word january in the middle of the text e.g "one january morning" and you want to count the number of occurances of cells containing the word january?
    Given

    {"Texas";
    "January";
    "Jan";
    "One January morning";
    "January blues in January"}

    in A1:A5 and

    {"January"}

    in B1,


    [1] in C1 enter:

    =IF(LEN(B1),COUNTIF(A1:A5,"*"&B1&"*"),"")

    [2] in C2 enter:

    =SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(LOWER(A1:A5),LOWER(B1),"")))/MAX(1,LEN(B1)))

    Added [2], in case you'd want to ask for that too.



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