VLOOKUP WITH COUNTIF AND RANGE

[Paula1784]

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

[#2]

@Paula1784 

What is the layout of the first sheet that you mention?

[#3]

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 0

[#4]

@Paula1784 

Thanks. 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.

[#5]

It's not giving me accurate results 😞

[#6]

@Paula1784 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#7]

Hi Hans, yes here is a link to the drive , hopefully you can access it.

https://docs.google.com/spreadsheets/d/1FQzwYphLJoyJKlN-eWtEN1SFIMC625oY/edit?usp=sharing_eip_m&rtpo...

[#8]

@Paula1784 

I can see the workbook, but downloading has been disabled, and copying as well, so I cannot do anything with it.

[#9]

try now, i updated the settings

[#10]

@Paula1784 

Like @HansVogelaar I can see it, but not download or copy. Seeing it, though, makes me think that a formula using COUNT and FILTER would work, assuming you have a current enough version of Excel so that FILTER works. This video should be sufficient to show you how to write the FILTER function to get the rows that meet your criteria.

[#11]

@Paula1784 

---

@Paula1784 wrote:
try now, i updated the settings

---

Nope, still the same.

[#12]

I simplified my data and figured it out 🙂 Thank you guys for taking the time to help me!