Forum Discussion

Paula1784's avatar
Paula1784
Copper Contributor
Apr 09, 2024

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

    • Paula1784's avatar
      Paula1784
      Copper Contributor
      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
      • HansVogelaar's avatar
        HansVogelaar
        MVP

        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.

Resources