Count Distinct Strings in a Column

[Stuck1]

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.

Team	Name
Team A	Ken
Team A	Ken
Team A	Ken
Team A	Bob
Team A	Jim
Team B	Kate
Team B	Sarah
Team B	Andy
Team B	Sarah

<tbody>
</tbody>

 

[Andrew Poulsom]

Does this help you?

Excel 2010
	A	B	C	D
1	Team	Name	Team A	3
2	Team A	Ken
3	Team A	Ken
4	Team A	Ken
5	Team A	Bob
6	Team A	Jim
7	Team B	Kate
8	Team B	Sarah
9	Team B	Andy
10	Team B	Sarah
Sheet2

Cell Formulas
Range	Formula
D1	{=SUM(IF(FREQUENCY(IF(A2:A10=C1,IF(B2:B10<>"",MATCH(B2:B10,B2:B10,0))),ROW(B2:B10)-ROW(B2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[pgc01]

Hi

Another option, similar to Andrew's without the Frequency():

=SUM(IF(A2:A10=C1,IF(B2:B10<>"",0+(MATCH(B2:B10,B2:B10,0)=ROW(B2:B10)-ROW(B2)+1))))

 

[Stuck1]

Thanks both,

I'm not quite sure what the row part of the formula is doing. I've tried to edit the formula, but it's not quite working. I suspect it's the final row syntax. Here's what I have:

=SUM(IF(FREQUENCY(IF('DQ DATA'!E:E=D11,IF('DQ DATA'!F:F<>"",MATCH('DQ DATA'!F:F,'DQ DATA'!F:F,0))),ROW('DQ DATA'!F:F)-ROW('DQ DATA'!F2)+1),1))

Hi

Another option, similar to Andrew's without the Frequency():

=SUM(IF(A2:A10=C1,IF(B2:B10<>"",0+(MATCH(B2:B10,B2:B10,0)=ROW(B2:B10)-ROW(B2)+1))))

 

[Stuck1]

Does this help you?

Array Formulas Cell Formula D1 {=SUM(IF(FREQUENCY(IF(A2:A10=C1,IF(B2:B10<>"",MATCH(B2:B10,B2:B10,0))),ROW(B2:B10)-ROW(B2)+1),1))} <TBODY> </TBODY> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<TBODY>
</TBODY>

Similar to my above response. I have :

=SUM(IF(FREQUENCY(IF('DQ DATA'!E:E=D11,IF('DQ DATA'!F:F<>"",MATCH('DQ DATA'!F:F,'DQ DATA'!F:F,0))),ROW('DQ DATA'!F:F)-ROW('DQ DATA'!F2)+1),1))


Both are entered as arrays.

 

[Oeldere]

With an pivot table I get this result.


Team Name
Team A Bob
Jim
Ken
Totaal Team A
Team B Andy
Kate
Sarah
Totaal Team B
Eindtotaal

 

[Andrew Poulsom]

This ROW('DQ DATA'!F2) should be ROW('DQ DATA'!F1). You should really try to avoid referencing entire columns.

 

[Stuck1]

Actually, my version works. It's just that my computer is running very slowly. Maybe if I limit the range it will be faster instead of being lazy with F:F.

Hi

Another option, similar to Andrew's without the Frequency():

=SUM(IF(A2:A10=C1,IF(B2:B10<>"",0+(MATCH(B2:B10,B2:B10,0)=ROW(B2:B10)-ROW(B2)+1))))

Thanks for the help everyone.

 

[pgc01]

I'm glad it helped ...

... and I agree completely with Andrew, avoid whole column references, it's inefficient.

Use instead something like

=SUM(IF(FREQUENCY(IF('DQ DATA'!E2:E10000=D11, ...