Count number of times of name appears in a column

**Ness78** · 07-31-2012, 09:15 PM

Hi guys,
I'm hoping someone can help me. I need to count the number of times a name appears in a column. For example, Column A has the following names:

gia
gia
gia
gia
gia
gia
jon
jon
Roger
Roger
Roger
Roger

I would like a formula so that Column B tells me the number of times the name in that cell appears in the rest of the column. So for gia the answer should be 6, for jon the answer should be 2 and for Roger the answer should be 4. I have data set is quite large that I imagine I could not use this formula =COUNTIF($A$2:$A$442,"gia") because to do this I would need to manually insert the name each time.

Can anyone help me out?

Thanks

**mike7952** · 07-31-2012, 09:25 PM

Ness78,

Just put your formula in column b but replace "gia" with A2 like this =COUNTIF($A$2:$A$442,A2) and drag down

**MarvinP** · 07-31-2012, 09:25 PM

Hi Ness and welcome to the forum.

See the attached with the answer.

**Ness78** · 07-31-2012, 09:34 PM

Thanks for the welcome and for getting back to me so quickly!!

Works a treat!! I've been trying to figure this one out for ages! Should have joined this forum months ago.

Thanks again

**Ace_XL** · 08-01-2012, 01:56 AM

Alternatively, use pivot tables..

**Cutter** · 08-01-2012, 08:14 AM

@ Ness78

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.