+ Reply to Thread
Results 1 to 5 of 5

Lookup specific worksheet and sum values within that meet criteria

  1. #1
    Registered User
    Join Date
    05-29-2018
    Location
    Southampton, England
    MS-Off Ver
    2007
    Posts
    5

    Lookup specific worksheet and sum values within that meet criteria

    I have a time-sheet spreadsheet (example attached).
    I am trying to create a summary sheet that states how much time an employee has worked/ taken per week.

    Each employee has their own named sheet: 'name one', 'name two', etc.
    The key information in these sheets are the week commencing date in col_B and the total minutes in col_F.

    On the summary sheet I would like a formula to lookup the named sheet (eg: Name One in A2), check the corresponding w/c date (C1), match it to the w/c dates in sheet 'name one' col_B, and sum the relevant values in the corresponding row(s) in col_F.

    I've had a look at various combinations of SUMPRODUCT, SUMIFS, INDIRECT and LOOKUP, but I can't find a combination that works and now my brain hurts. Any help would be much appreciated.

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-06-2018
    Location
    Rio
    MS-Off Ver
    2016
    Posts
    24

    Re: Lookup specific worksheet and sum values within that meet criteria

    try this:

    =SUMIFS('Name One'!$F$5:$F$34,'Name One'!$B$5:$B$34,Summary!C$1)

  3. #3
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Office 365
    Posts
    2,801

    Re: Lookup specific worksheet and sum values within that meet criteria

    Please try at C2 drag across and down

    =SUMIFS(INDIRECT("'"&$A2&"'!F5:F34"),INDIRECT("'"&$A2&"'!B5:B34"),C$1)

  4. #4
    Registered User
    Join Date
    05-29-2018
    Location
    Southampton, England
    MS-Off Ver
    2007
    Posts
    5

    Re: Lookup specific worksheet and sum values within that meet criteria

    Massive thanks Bo, this is exactly what I was looking for!

  5. #5
    Registered User
    Join Date
    05-29-2018
    Location
    Southampton, England
    MS-Off Ver
    2007
    Posts
    5

    Re: Lookup specific worksheet and sum values within that meet criteria

    Thanks rjcarlantonio, this does work as well, but it's a bit more time-consuming for changing the name each time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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