Countif (2002)

Topic

New Reply

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?

Reply | Quote

Replies

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
[Fmr MS MVP - Word]

Reply | Quote

That works great – Thanks!!

Regards

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote