+ 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 Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    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)

Similar Threads

  1. [SOLVED] lookup values that meet certain criteria
    By nonstop5000 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-22-2015, 10:11 PM
  2. Lookup return values if meet criteria?
    By lissamel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-31-2015, 10:21 AM
  3. [SOLVED] Lookup Values which meet two or more criteria and return multiple matches horizontally
    By josetmg in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-03-2015, 01:16 AM
  4. LOOKUP Data across a range of cells to meet a specific criteria.
    By ads111ads in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-30-2014, 01:15 PM
  5. Count values which meet specific criteria
    By araujo3rd in forum Excel General
    Replies: 10
    Last Post: 12-08-2012, 04:45 AM
  6. [SOLVED] How do I use a lookup table to sum values for specific criteria?
    By NJLExcel1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-08-2012, 02:57 PM
  7. Delete rows which do not meet specific criteria (text values)
    By dim06 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2012, 09:54 AM

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