Calculate number of duplicates (MrLenze answer is gone !!!)

[ChrisTa]

Hi everyone,

I am quite stuck with a Excel work

it contents about 40'000 entries and I need to count specifics things but including constraints.

Unique ID Name Member Location
XXX 001 X 1 London
XXX 002 Y 0 London
XXX 001 Z 1 Manchester
XXX 003 A 1 Manchester
XXX 004 B 1 Liverpool
XXX 001 C 0 London

For example, I have to find how many people with a unique ID are member (1=member 0=not member). In this case, the correct answer would be 3 (because the ID XXX 001 appears twice and only count once)

Besides, I am also looking for a formula to count the same thing adding the location. For example, how many unique ID are member AND live in London. Here, only 1 because even if XXX 001 (C) is not member we count XXX 001 (X).

I hope that my explanations were not too bad and I really count on you guys because I am stuck with this since this morning....

Thanks a lot

 

[ChrisTa]

Thanks a lot Lenze

 

[ChrisTa]

???? Where is Lenze's answer ??? Have I done something ???...I should stop dringing coffee so much...

 

[lenze]

Sorry, my fault. I accidentally deleted it!!

Here is the PTs again

  A         B    C      D          
1 Unique ID Name Member Location   
2 xxx 001   X    1      London     
3 xxx 002   Y    0      London     
4 xxx 001   Z    1      Manchester 
5 xxx 003   A    1      Manchester 
6 xxx 004   B    1      Liverpool  
7 xxx 001   C    0      London     

Sheet1

[Table-It] version 07 by Erik Van Geit

   A             B                C      D     
11 Count of Name                               
12 Unique ID     Location         Member Total 
13 xxx 001       London           0      1     
14                                1      1     
15               London Total            2     
16               Manchester       1      1     
17               Manchester Total        1     
18 xxx 001 Total                         3     
19 xxx 002       London           0      1     
20               London Total            1     
21 xxx 002 Total                         1     
22 xxx 003       Manchester       1      1     
23               Manchester Total        1     
24 xxx 003 Total                         1     
25 xxx 004       Liverpool        1      1     
26               Liverpool Total         1     
27 xxx 004 Total                         1     
28 Grand Total                           6     

Sheet1

[Table-It] version 07 by Erik Van Geit

Sorry
lenze