Count appointments per each team member per time frame

[jsterra]

Hi all, please help:

I work in a hospital. Front office team answers the phone and books doctor appointments for customers. Our system registers the time and date of the appointment and who booked it. I want to count how many appointments were booked by each team member, per time frame.

On the data I have:

• Column D: Team member names
• Column I: Date
• Column J: Time

I am able to count the total appointments booked per time frame e.g. 8:30-9:00 using:
=COUNTIFS($J$2:$J$1048576;">=8:30";$J$2:$J$1048576;"<=9:00")

I am able to count the total appointments booked per team member. To achieve this I added column L where every cell is number 1 and then for e.g. Monica it is: =+SUMIFS($L$2:$L$1048576;$D$2:$D$1048576;"Mónica Gil")

Now how can I count how many appointments were booked by Monica at time frame 8:30-9:00? Any suggestions?

I’ve tried mixing the formulas in a bunch of ways but I only get zeros or errors… Please help.

Thank you very much in advance.
J

 

[James006]

Hi,

Just make your life a lot easier ... by inserting a Pivot Table ...

Hope this will help

 

[Toadstool]

Try adding two more conditions to the SUMIFS: ;$J$2:$J$1048576;">=08:30";$J$2:$J$1048576;"<=09:00")