Hope someone can help with this. I want to count distinct/unique names (strings) in a coloumn, but I also need to account for which team the person is in. My data looks a little like this (see below), so in this example I'd like to return that there are 3 people in each team and rule out the duplicates.

I've been playing with CPearsons formula and come up with this, but I can't figure out the first part of the formula where I add the criteria for the team. The sumproduct is just trial and error.

=SUMPRODUCT(--('DQ DATA'!$E:$E=D11))*(SUM(IF(FREQUENCY(IF(LEN('DQ DATA'!A:A)>0,MATCH('DQ DATA'!A:A,'DQ DATA'!A:A,0),""), IF(LEN('DQ DATA'!A:A)>0,MATCH('DQ DATA'!A:A,'DQ DATA'!A:A,0),""))>0,1)))

Thanks for any help.

[TABLE="width: 500"]

<tbody>[TR]

[TD]

**Team**[/TD]

[TD]

**Name**[/TD]

[/TR]

[TR]

[TD]Team A[/TD]

[TD]Ken [/TD]

[/TR]

[TR]

[TD]Team A[/TD]

[TD]Ken[/TD]

[/TR]

[TR]

[TD]Team A[/TD]

[TD]Ken[/TD]

[/TR]

[TR]

[TD]Team A[/TD]

[TD]Bob[/TD]

[/TR]

[TR]

[TD]Team A[/TD]

[TD]Jim[/TD]

[/TR]

[TR]

[TD]Team B[/TD]

[TD]Kate[/TD]

[/TR]

[TR]

[TD]Team B[/TD]

[TD]Sarah[/TD]

[/TR]

[TR]

[TD]Team B[/TD]

[TD]Andy[/TD]

[/TR]

[TR]

[TD]Team B[/TD]

[TD]Sarah[/TD]

[/TR]

</tbody>[/TABLE]