Excel counting problem where there might be 2 entries for one location?

  • Thread starter Pheasant Plucker®
  • Start date
P

Pheasant Plucker®

Hi there,

I have a spreadsheet in which Column A looks like this;

0034-MILTON KEYNES
0034-MILTON KEYNES
0035-MARBLE ARCH
0035-MARBLE ARCH
0039-EAST HAM
0040-REGENT ST
0043-STAINES
0051-PENGE
0052-BEDFORD

You will notice there may be either one entry for a particular location or
there may be two entries for the same location.

Now I need to total up the amount of individual locations taking into
account in the example above for example there are a total of 9 entries but
only 7 of those are individual locations.

Is this even possible and if so how would I achieve this please?
 
D

Dave Peterson

If you wanted to count the number of unique entries in that range, you could use
a formula like:

=sumproduct((a1:a10<>"")/countif(a1:a10,a1:a10&""))
(you can only use the entire column in xl2007)
 
Top