Count Distinct Strings in a Column

Stuck1

Board Regular
Joined
Sep 3, 2009
Messages
73
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]
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Does this help you?


Excel 2010
ABCD
1TeamNameTeam A3
2Team AKen
3Team AKen
4Team AKen
5Team ABob
6Team AJim
7Team BKate
8Team BSarah
9Team BAndy
10Team BSarah
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
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))))
 
Upvote 0
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))))
 
Upvote 0
Does this help you?

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]D1
[/TH]
[TD="align: left"]{=SUM(IF(FREQUENCY(IF(A2:A10=C1,IF(B2:B10<>"",MATCH(B2:B10,B2:B10,0))),ROW(B2:B10)-ROW(B2)+1),1))}
[/TD]
[/TR]
</TBODY>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
[/TD]
[/TR]
</TBODY>[/TABLE]

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.
 
Upvote 0
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
 
Upvote 0
This ROW('DQ DATA'!F2) should be ROW('DQ DATA'!F1). You should really try to avoid referencing entire columns.
 
Upvote 0
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.
 
Upvote 0
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, ...
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,877
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top