Hi all,
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]
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]