Formula to count names

[jdepriest]

I searched the forums but haven't found an answer that works for me. I'm trying to count the number of times a name appears in a column.

A1 - Tom
A2 - Tom, Harry
A3 - Joe, Tom
A4 - Tom
A5 - Tom, Bill

I want to count Tom in the list.

I've tried =countif(A1:A5,"Tom") which give the answer of 2.

On another forum I found this one =SUMPRODUCT(--(COUNTIF(OFFSET(A1:CH1,ROW(A1:A5)-ROW(A1),),"Tom")>0)) also gave me the answer 2.

Ideas??

[Fettertiger]

Hi jedepriest,

try adding wildcards:
=COUNTIF(A:A;"*Tom*")

Regards

Theo
__________________
Please consider:

Thanking those who helped you. Click the scales icon in the upper right corner of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Edit/Delete Message

[Leith Ross]

Hello jdepriest,

This is easy to do with CountIf because it supports the use of wild card characters in the criteria. The criteria is not case sensitive so either "Tom" or "tom" will work. This will count all occurances of the letters "tom" in the cell and just whole words.

=COUNTIF(A1:A5,"*" & "Tom" & "*")

[jdepriest]

IT WORKED!!!

Thanks!