Distinct Count

[Smithsc]

I am trying to find a formula that will do a distinct count over a set of data:

Person Deal
A a1
A a1
A z1
B b1
B b1
C c1
C c2
C c3
D d1
D d1
D d1
D d2
D d3
E e1
E e2
E e2

Basically what I'm after is to be able to count the number of different deals each person has done. The result would look something like this:

Person Count of Diff Deals
A 2
B 1
C 3
D 3
E 2

Any help would be greatly appreciated

Stuart.

 

[SkipVought]

hi,

Check out the PivotTable Wizard. Fits the bill to a T!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Might be better to use the Advanced filter to generate a unique list in column D.

Then use this formula, using Named Ranges on your original table...
[tt]
F2: =sumproduct((Person=D2)*(Deal=E2))
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}