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

Try this array formula

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

--
__________________________________
HTH

Bob

"carl" wrote in message
...
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

Hello,

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

Regards,
Bernd

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)=R OW($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



"Bob Phillips" wrote:

Try this array formula

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

--
__________________________________
HTH

Bob

"carl" wrote in message
...
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

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"carl" wrote:

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

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.

--
Biff
Microsoft Excel MVP


"carl" wrote in message
...
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