Monthly Hours Tracking

[momoneymath]

Hey guys,

I've been pretty stumped on this one problem: I need a formula that will sum up all of the hours worked by an employee for a month. I'll be picking the name from a drop down list, and the idea is that it will look up all the relevant data in a separate spreadsheet. It gets tricky because on the spreadsheet it will lookup from, hours are done by day (so i will need to sum up a row), and there is also multiple rows for one name because the sheet is set up by location. So basically, I need to lookup values on multiple rows AND columns, then sum all of them up. Would this be possible to do in 1 cell? Let me know what you think. Thanks.

[etaf]

i assume a sumif() would not work
But not sure i understand the multiple columns - so

Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "Choose File" (top Left corner).
Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[momoneymath]

Here is a sample for further clarification. I've added a formula that works, but stops after one row. Thanks.

[etaf]

you need to change the format so that the heading are actual dates
and then you can use
=SUMPRODUCT(((C$1<=Hours!$D$1:$BT$1)*(D$1>Hours!$D$1:$BT$1))*(Hours!$B$2:$B$20=$A2)*Hours!$D$2:$BT$20)

you can always format the cells to display the dates however, you like - BUT they must be dates for this to work

see example attached
and also this website
http://www.get-digital-help.com/2009...eria-in-excel/

[momoneymath]

Worked, thanks a lot. You're a life saver.

[etaf]

you are welcome
thanks for rep