• July 17, 2017
    Announcement: New site design for Microsoft Community

    In July, Microsoft will roll out the first of ongoing site improvements aimed to modernize Microsoft Community and help customers get the most out of their community experience.

    • During the roll out period, you may see the old or new site design depending on your location

    • We expect the roll out to finish by 31 July

    Note: Past private message conversations will not move to the new site design. Please save any private messages you would like to keep.

     Learn more about the upcoming site improvements in this thread.

    Thank you for being part of Microsoft Community!


Count unique entries in one column when they fall between time frames in two other columns

myer84 asked on

Hello all,


I'm working on a report where I'm evaluating production times from a production report I receive daily.


Column C has the list of producing associates, Column G the production start time, and Column H the production end time (format: H:MM:SS AM/PM). The list of associate names on column C is not unique, meaning that an associate appears more than once.


What I'm trying to figure out is how many unique associates on column C produced between the hours of 7 AM and 8 AM. For example; below the results show that between 7 AM and 8 AM there are 4 production results, but only Mary and Bill produced during the time frame. I would like a count of unique associates between the time frame. In this example, my result would be 2.


Associate Start Time End Time
Mary 7:13:00 AM 7:17:00 AM
Mary 7:14:00 AM 7:37:00 AM
Mary 7:17:00 AM 7:51:00 AM
John 7:18:00 AM 8:12:00 AM
Bill  7:30:00 AM 7:45:00 AM
Bill  7:30:00 AM 8:10:00 AM
Mary 7:37:00 AM 8:10:00 AM
John 7:37:00 AM 8:25:00 AM





1 person had this question

Abuse history

The answered status icon Answer
Ron Coderre replied on

Thank you Ron. I tried your suggestion; however, my end result is 1. I made sure to enter the formula as an ARRAY and double checked my ranges. No idea why the result is not pulling correctly. Any ideas?



This was my setup:

A1: 7:00:00 AM

A2: 8:00:00 AM






Start Time End Time
7:13:00 AM 7:17:00 AM
7:14:00 AM 7:37:00 AM
7:17:00 AM 7:51:00 AM
7:18:00 AM 8:12:00 AM
7:30:00 AM 7:45:00 AM
7:30:00 AM 8:10:00 AM
7:37:00 AM 8:10:00 AM
7:37:00 AM 8:25:00 AM


Using the above, this ARRAY FORMULA:

B1: =COUNT(1/FREQUENCY(IF((H2:H9<=A2)*(G2:G9>=A1),MATCH(C2:C9,C2:C9,0),0),ROW(C2:C9)-1))

returns: 2


Is there still an issue


Ron Coderre
Microsoft MVP - Excel
Be the first person to mark this helpful

Abuse history