Counting Names in cells

Posted by Corey on September 01, 2001 10:36 AM

Let's say I have a range (A3:A:40). Within each cell there are multiple names in those cells. I want to count the number of times, for example, John Smith appears. Tried the countif function but countif only counts the # of occurences by cell. Any help would be greatly appreciated.

Thanks.

Posted by Aladin Akyurek on September 01, 2001 11:00 AM

Corey,

Try:

=SUMPRODUCT(ISNUMBER(SEARCH("john smith",A3:A40))+0)

Aladin

=========

Posted by Corey on September 01, 2001 11:34 AM

Worked like a charm, Thanks.

Posted by Kevin James on September 01, 2001 2:00 PM

didn't work for me

Hi Aladin,

(Long time, no see.) I tried your formula suggestion and it didn't work per required specs:

A1 contains your formula
A3: john smith john smith john smith

Based on what I read, the answer should be 3. It was only 1.

Posted by Aladin Akyurek on September 01, 2001 2:51 PM

Re: didn't work for me

Hi Kevin,

Happy to see you back.

I read the specs intendedly involving data like

{"John Smith Kevin James John Doe";
"Kevin James Aladin Akyurek";
"John Doe John Smith"}

I thought Corey has one or more names per cell, but never the same name more than once in a single cell.

Applying SEARCH would not do the job if the way you read it would be the case for it can only return a result about the first occurrence of the item of interest, just like the lookup functions.

Aladin