Count Unique based on other value

[reganshaw]

Hi All,
I haven't found the right solution in the previous posts and hop you can help

I have 2 columns with data

Column A = Route Name

CELL A2 - Route 1
CELL A3 - Route 1
CELL A4 - Route 2
CELL A5 - Route 2
CELL A6 - Route 2
CELL A7 - Route 2

Column B = Stop ID

CELL B2 - W1
CELL B3 - W3
CELL B4 - X1
CELL B5 - X1
CELL B6 - X2
CELL B7 - X2

I need a formula that will tell me how many unique Stop ID's for each route.

 

[Aladin Akyurek]

Hi All,
I haven't found the right solution in the previous posts and hop you can help

I have 2 columns with data

Column A = Route Name

CELL A2 - Route 1
CELL A3 - Route 1
CELL A4 - Route 2
CELL A5 - Route 2
CELL A6 - Route 2
CELL A7 - Route 2

Column B = Stop ID

CELL B2 - W1
CELL B3 - W3
CELL B4 - X1
CELL B5 - X1
CELL B6 - X2
CELL B7 - X2

I need a formula that will tell me how many unique Stop ID's for each route.

Let E2 house Route 1...

F2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$2:$B$7<>"",IF($A$2:$A$7=E2,
   MATCH("~"&$B$2:$B$7,$B$2:$B$7&"",0))),
    ROW($B$2:$B$7)-ROW($B$2)+1),1))

 

[reganshaw]

Thank you!

 

[Aladin Akyurek]

Thank you!

You are welcome. Thanks for providing feedback.

 

[reganshaw]

Just another quick question if you don't mind... why the requirement for using ctrl+shift+enter?