Question
213 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