Counting unique values based on criteria

CAHIGHAM

New Member
Joined
Dec 28, 2017
Messages
11
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.

DATE Route
2/27/2018 BCG1A
2/27/2018 BCG1A
2/27/2018 BCG1A
2/27/2018 BCG1A
2/27/2018 BCG1A
2/27/2018 BCG1A
2/27/2018 BCG1A
2/27/2018 BCG1A
2/27/2018 BCG2A
2/27/2018 BCG2A

<colgroup><col><col></colgroup><tbody>
</tbody>






<colgroup><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 
Last edited:

CAHIGHAM

New Member
Joined
Dec 28, 2017
Messages
11
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

New Member
Joined
Dec 28, 2017
Messages
11
Sorry I missed the CTL+SHIFT+ENTER which you indicated from the start. My apologies. Thanks for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,658
Messages
5,549,265
Members
410,905
Latest member
Extjel
Top