• 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!

 
Question
214 views

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

 

Thanks

 

 

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?

 

Thanks


This was my setup:

A1: 7:00:00 AM

A2: 8:00:00 AM

 

C1:C9

Associate
Mary
Mary
Mary
John
Bill 
Bill 
Mary
John

 

G1:H9

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

Regards,

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

Abuse history


progress