Results 1 to 7 of 7

Thread: Countif (2002)

  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Countif (2002)

    Loungers - I need to be able to count a range of cells that contains various text values - ie x days overdue. So need to be able to count cells that contain "days overdue".

    I'm sure this is a simple task when you know how - any suggestions?

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Countif (2002)

    Hi Dean,

    You could use the following array formula:
    =COUNT(IF(FIND("days overdue",A1:A100),1,))
    (use Ctrl-Shift-Enter instead of Enter to activate)

    Change 'A1:A100' to match your range.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (2002)

    That works great - Thanks!!

    Regards

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Countif (2002)

    Hi Dean,

    Just in case it's of any importance, the FIND function is case sensitive. For a formula that isn't case-sensitive, replace 'FIND' with 'SEARCH'

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (2002)

    J. Walkenbach offers a different solution.

    Suppose Range (A1:A1000) is named Data, and "text" is the name of cell B1. Then if not case sensitive, the formula is:

    =COUNTIF(data,text) (not array entered)

    But if it is case sensitive, then this formula works:

    =SUM(IF(EXACT(Data,text),1)) which is array entered (CSE)

    My question: Is there an advantage of this case sensitive formula vs. what was posted?

  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: Countif (2002)

    The exact will not work if you want to look at cells that contain the phrase of interest. It will only find things that are exactly equal. In this respect if could be faster since it can quit earlier when searching. once a character does not match it is done, while the find has to continue looking in case it might be later.

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif (2002)

    Thanks, that makes sense.

    That must be why he offers this one for case sensitive partial cell contents match:

    =SUM(IF(LEN(data)-LEN(SUBSTITUTE(data,text,""))>0,1)) (array entered)

Posting Permissions

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