# Lookup specific worksheet and sum values within that meet criteria

1. ## 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.

2. ## 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. ## 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. ## Re: Lookup specific worksheet and sum values within that meet criteria

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

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.

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