Counting unique values based on criteria

[CAHIGHAM]

Hello,

I have two columns in my workbook

Column A - lists dates
Column B - lists route numbers

There are several duplicates since there is a row for each shipment done by the route each day.

I need a formula to tell me how many unique days the route operated. See below - BCG1A operated 1 day and BCG2A operated 1 day.

[TABLE="width: 149"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 149"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD] Route [/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2018[/TD]
[TD] BCG1A[/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2018[/TD]
[TD] BCG1A[/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2018[/TD]
[TD] BCG1A[/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2018[/TD]
[TD] BCG1A[/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2018[/TD]
[TD] BCG1A[/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2018[/TD]
[TD] BCG1A[/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2018[/TD]
[TD] BCG1A[/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2018[/TD]
[TD] BCG1A[/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2018[/TD]
[TD] BCG2A[/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2018[/TD]
[TD] BCG2A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]




[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In G2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($B$2:$B$11=$F2,$A$2:$A$11),$A$2:$A$11),1))

where F2 houses a value like BCG1A.

 

[CAHIGHAM]

I tried the formula a few times but it doesn't work unfortunately - there is an error in the way it is written...I can't figure out what it is

 

[CAHIGHAM]

Sorry I missed the CTL+SHIFT+ENTER which you indicated from the start. My apologies. Thanks for your help!

 

[Aladin Akyurek]

Sorry I missed the CTL+SHIFT+ENTER which you indicated from the start. My apologies. Thanks for your help!

No problem. You are very welcome.