Forum Discussion
VLOOKUP WITH COUNTIF AND RANGE
I have an excel sheet that has several employee names for a 90 day period, it will show how many minutes late they were each day for 90 days. on another sheet i have their names pre filled and need to do a vlookup on their name on the previous sheet and return the count of how many times they were late between 4 and 14 minutes. Heeeeeelp
11 Replies
What is the layout of the first sheet that you mention?
- Here is a sample below:
Same officer with each swipe in entry over 90 days, they have a start time, and then a punch in time, if that is between 4 mins and 14 mins I need to count that occurence.
Employee Name Date Start Time In Punch # of MINS
ABDUS SAMI, MUNTASIR 1/3/2024 04:00 03:53 -7
ABDUS SAMI, MUNTASIR 1/4/2024 04:00 03:53 -7
ABDUS SAMI, MUNTASIR 1/8/2024 04:00 03:53 -7
ABDUS SAMI, MUNTASIR 1/10/2024 04:00 03:53 -7
ABDUS SAMI, MUNTASIR 1/15/2024 04:00 03:53 -7
ABDUS SAMI, MUNTASIR 1/16/2024 04:00 03:53 -7
ABDUS SAMI, MUNTASIR 1/22/2024 04:00 03:53 -7
ABDUS SAMI, MUNTASIR 1/2/2024 04:00 03:54 -6
ABDUS SAMI, MUNTASIR 1/5/2024 04:00 03:54 -6
ABDUS SAMI, MUNTASIR 1/9/2024 04:00 03:54 -6
ABDUS SAMI, MUNTASIR 1/12/2024 04:00 03:54 -6
ABDUS SAMI, MUNTASIR 1/19/2024 04:00 03:54 -6
ABDUS SAMI, MUNTASIR 1/1/2024 04:00 03:55 -5
ABDUS SAMI, MUNTASIR 1/24/2024 04:00 03:55 -5
ABDUS SAMI, MUNTASIR 1/29/2024 04:00 03:55 -5
ABDUS SAMI, MUNTASIR 1/11/2024 04:00 03:56 -4
ABDUS SAMI, MUNTASIR 1/18/2024 04:00 03:56 -4
ABDUS SAMI, MUNTASIR 1/30/2024 04:00 03:56 -4
ABDUS SAMI, MUNTASIR 1/17/2024 04:00 03:57 -3
ABDUS SAMI, MUNTASIR 1/23/2024 04:00 03:57 -3
ABDUS SAMI, MUNTASIR 1/25/2024 04:00 03:57 -3
ABDUS SAMI, MUNTASIR 1/26/2024 04:00 03:57 -3
ABDUS SAMI, MUNTASIR 1/31/2024 04:00 03:57 -3
ABDUS SAMI, MUNTASIR 2/1/2024 04:00 04:00 0Thanks. Let's say that sheet is named Data Sheet.
In B2 on the other sheet:
=COUNTIFS('Data Sheet'!$A$2:$A$10000, A2, 'Data Sheet'!$E$2:$E$10000, "<=-4", 'Data Sheet'!$E$2:$E$10000, ">=-14")
Fill down.
