# Thread: Countif (2002)

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

3. ## Re: Countif (2002)

That works great - Thanks!!

Regards

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

5. ## 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. ## 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. ## 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
•