Lookup

**MisterKrister** · 03-08-2010, 01:59 PM

I've created a weekly rota for my barstaff in excel 2003 and need some help to finish it off as I would like it.
It's a basic format where it will show name, start time, end time and number of hours worked for each member of staff for each day. So for example in the Sunday column cell B4 shows "Chloe", cell B5 shows "1100" (start time), cell C5 shows "1800" and cell C4 shows "7" (hours worked that day).
Obviously there are many members of staff doing different shifts on different days and this will change from week to week so what I'm trying to do is tally the total hours each staff member has worked by the end of the week. Is it possible for me to search for a staff member's name and tally the figures in the adjacent cells each time it is found?

I can attach my current file if this helps. Many thanks in advance.

**darkyam** · 03-08-2010, 02:21 PM

Yes, I think an attachment would greatly help us, particularly in making the formula draggable. Thanks.

**MisterKrister** · 03-08-2010, 02:44 PM

No problems, hopefully should be attached here.

**darkyam** · 03-08-2010, 04:40 PM

Try =SUM(($B$4:$T$40=W4)*IF(ISNUMBER($C$4:$U$40),($C$4:$U$40))), confirmed with Control+Shift+Enter in X4, copied down.

**MisterKrister** · 03-08-2010, 05:00 PM

Sir, I am astonished and hugely grateful. I owe you a beer.

Thanks so much!

**darkyam** · 03-08-2010, 05:09 PM

Had a bit of a mental lapse. There's an easier way: =SUMIF($B$4:$T$40,W4,$C$4:$U$40). This doesn't require confirmation with CSE and is a bit more efficient.