Unique Count

  • Thread starter Thread starter carl
  • Start date Start date
C

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
 
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))
 
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))
Click to expand...

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
 
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.
 
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.
 
Back
Top