Excel - Count unique entries

[Brian555]

PLEASE HELP with what seems to be a simple task but one that I cannot figure out.

I have a table in which one column lists UK cities e.g.
Manchester
London
Manchester
Leeds
Birmingham
Leeds
Leeds
Newcastle

My question is how can I count how many times each city appears in the column without using a great many COUNTIF formula's (the table is serveral hundred rows long and contains a large number of cities).

 

[bluedragon2]

A pivot table will give you the information you seek.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

You can use this array formula:
=SUM(1/COUNTIF(A1:A25,A1:A25))

[attn]NOTE[/attn]: Enter by using [Ctrl]+[Shift]+{Enter]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[bluedragon2]

John, Does that give how many times each city appears or does it count how many unique cities?



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

D'oh.

I suppose I should really start reading the posts before I answer.

I agree, a pivot table would be the easiest way. The only other thing that comes to mind would be to do an advanced filter, copy the visible cells and then paste them elsewhere on the sheet, and then doing your CountIfs.

Pivot table is much easier and faster.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[kermitforney]

It counts Unique cities so if there are 4 unique cities this formula returns the value 4.

 

[KenWright]

Just drag Cities into Row fields and then also drag it again from the list into the Data field ensuring it is set to Count and you have your counts.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Brian555]

Dear all

Many and sincere thanks to everyone who helped. The pivot table gave me the answer in five minutes.

Brian