Excel formula needed to count a list of names [Solved/Closed]

lut1n - Mar 9, 2010 at 11:47 AM - Latest reply:  LINA
- Jul 25, 2015 at 06:18 AM
Hi

This is probably an easy one for the pro's,

basically I have a list of names that will change as reports are generated. The report will include the same name multiple times, so joebloggs could appear 10 times. I need a formula to scan COL:A that has the list of names.

In COL:B I would like excel to display each of the different names that are in COL:A but only once. So for example JOEBLOGGS would appear in B2 only once. Then is COL:C I would like the total amount of times that name appeared in that list. so for example:

A B C
1 joebloggs JOEBLOGGS 5
2 joebloggs
3 joebloggs
4 joebloggs
5 joebloggs

These names will vary so I cannot specify the names using countif function. I need excel to populate the names in B automatically.

any help would be appreciated.
Thanks
See more 

8 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Mar 9, 2010 at 09:21 PM
31
Thank you
suppose your data is like this from A1 to A9(note colufmn heading -this is necessary)
names
a
a
a
a
s
s
d
d

click data(menu)-filter-advancedfilter

choose radio button at the top "copy to another location"
against list range click the icon at the right end of the small window and highlight A1 to A9
leave blank criteria range
against "copy to"-click the icon at the right end of the window and select some empty cell e.g. D1
choose radio button "unique records only" at the bottomleft
click ok
you will get D1 to D4
names
a
s
d
in E2 copy paste this formla (repeat E2)
=COUNTIF(A2:A9,D2)
copy E2 down.

you will get
names frequency
a 4
s 2
d 2


will this do?

you can modify this help to suit you

Thank you, venkat1926 31

Something to say? Add comment

CCM has helped 1676 users this month

this is really helpful. Thxs a lot!
thank you so much
however it does not work for the names longer than 3characters.
thanks lifesaver
0
Thank you
Have you worked with PivotTables before? It's an easy way to do a count like this, you may have to do a work around to get the date into column B. But you should take a look at how to work with PivotTables to see it works for you: http://office.microsoft.com/...

Cheers,
Andy
MSFT Office Outreach