Unique Count

[carl]

My data is like so:

Stock Code
AAPL AB
AAPL AB
AAPL AB
AAPL AB
AAPL AB
GM T2
GM T3
GM T2
GM T4


I am trying to create a table that will give me the Unique Count Of Codes
Per Stock - here's an example based on the data above:


Stock UniqueCodeCount
AAPL 1
GM 3


Thank you in advance

 

[Bob Phillips]

Try this array formula

=COUNT(1/IF($A$2:$A$10=$J1,MATCH($B$2:$B$10,$B$2:$B$10,0)=ROW($B$2:$B$10)-ROW($B$2)+1))

 

[Bernd P]

Hello,

We had this before:
http://sulprobil.com/html/count_unique.html

Regards,
Bernd

 

[Teethless mama]

Your formula returns incorrect result with this data

Stock Code
AAPL T2
AAPL T3
AAPL AB
AAPL AB
AAPL AB
GM T2
GM T3
GM T2
GM AB

=COUNT(1/IF($A$2:$A$10=$J1,MATCH($B$2:$B$10,$B$2:$B$10,0)=ROW($B$2:$B$10)-ROW($B$2)+1))

GM - returns big fat "0"


Try this formula:

=SUM(N(FREQUENCY(IF($A$2:$A$10=J1,MATCH($B$2:$B$10,$B$2:$B$10,)),MATCH($B$2:$B$10,$B$2:$B$10,))>0))

ctrl+shift+enter, not just enter

 

[Shane Devenshire]

Hi,

Here is another version of the same ideas:

=SUM(N(FREQUENCY(IF(A$1:A$9=D1,MATCH(B$1:B$9,B$1:B$9,)),MATCH(B$1:B$9,B$1:B$9,))>0))

Array entered - press Shift+Ctrl+Enter to enter the formula
In this example my data started on row 1 and AAPL was in D1. Copy down as
far as necessary.

 

[T. Valko]

Assuming no empty cells in the Code column.

D2 = APPL

Try this array formula** :

=SUM(IF(FREQUENCY(IF(A$2:A$10=D2,MATCH(B$2:B$10,B$2:B$10,0)),ROW(B$2:B$10)-ROW(B$2)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.