+ Reply to Thread
Results 1 to 4 of 4

Look up a specific Name & Count Times Between 2 Specified Times (15 min intervals)

  1. #1
    Registered User
    Join Date
    09-03-2012
    Location
    Australia, Victoria
    MS-Off Ver
    Excel 2003
    Posts
    25

    Look up a specific Name & Count Times Between 2 Specified Times (15 min intervals)

    Hi All,

    This is a tricky one but just need some help with a formula, to look up a Name from the Output tab A column and count the times between 2 specified Times (15 min intervals) which is in rows 1 and 2 and match the data from the input tab to return the data in Output B3 cell.

    I have an example sheet attached.
    exampleworkflow.xlsx

    Thank you in advance

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,745

    Re: Look up a specific Name & Count Times Between 2 Specified Times (15 min intervals)

    Hi,

    In cell B3 and copy across and down as required:

    =SUMPRODUCT((Input!$A$2:$A$389=Output!$A3)*(MOD(Input!$B$2:$B$389,1)>=Output!B$1)*(MOD(Input!$B$2:$B$389,1)<=Output!B$2))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Look up a specific Name & Count Times Between 2 Specified Times (15 min intervals)

    hi arrekkusu03, another alternative:
    =SUMPRODUCT(--(Input!$A$2:$A$389=$A3),--(MOD(Input!$B$2:$B$389,1)>=B$1),--(MOD(Input!$B$2:$B$389,1)<=B$2))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Look up a specific Name & Count Times Between 2 Specified Times (15 min intervals)

    What is the expected output of B3 cell?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count number a times a specific value appears consecutively.
    By kww0027 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-03-2013, 06:17 PM
  2. Need formula to count how many times a specific name is entered in a column
    By JoeBlakkk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-04-2012, 01:36 PM
  3. Need to count records for specific days and times
    By cdholt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-25-2011, 08:39 PM
  4. Macro Loop Find Specific Times, Extract Date, Average Values of Times
    By ExcelQuestFL in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-25-2010, 02:50 PM
  5. [SOLVED] Count names-how many times a specific name appears
    By Farrel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-14-2005, 04:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1