# Count Distinct Strings in a Column

#### Stuck1

##### Board Regular
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>

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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.

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 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))))

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.

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

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

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.

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

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

Replies
14
Views
701
Replies
3
Views
811
Replies
1
Views
275
Replies
21
Views
555
Replies
8
Views
2K

1,196,309
Messages
6,014,586
Members
441,828
Latest member
cofracr

### 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.

### Which adblocker are you using?

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

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