# VLOOKUP WITH COUNTIF AND RANGE

Copper Contributor

# 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

# Re: VLOOKUP WITH COUNTIF AND RANGE

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

# Re: VLOOKUP WITH COUNTIF AND RANGE

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

# Re: VLOOKUP WITH COUNTIF AND RANGE

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.

# Re: VLOOKUP WITH COUNTIF AND RANGE

It's not giving me accurate results :(

# Re: VLOOKUP WITH COUNTIF AND RANGE

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?

# Re: VLOOKUP WITH COUNTIF AND RANGE

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

# Re: VLOOKUP WITH COUNTIF AND RANGE

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

# Re: VLOOKUP WITH COUNTIF AND RANGE

try now, i updated the settings

# Re: VLOOKUP WITH COUNTIF AND RANGE

Like @Hans Vogelaar 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.

# Re: VLOOKUP WITH COUNTIF AND RANGE

@Paula1784 wrote:
try now, i updated the settings

Nope, still the same.

# Re: VLOOKUP WITH COUNTIF AND RANGE

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