I need a formula for a sum of resource hours across multiple tabs

**jackiemariee** · 01-31-2019, 12:14 PM

I need help creating a formula that can help with resource management. What I have set up so far is a master tab listing all of the resources down the y axis with the date of the beginning of the work week across the x axis. See below image.

Resources.JPG

Next, I have a separate tab for each project. The tabs will look similar to the master resource tab, however, because each project starts at different dates it will only display the weeks of the project's duration.

Project 1.JPG

Project 2.JPG

Project 3.JPG

Here's where I need help. I would like a formula for the master resource tab that will search against a person's name within each project tab and look for a particular date and take the sum of all of the hours that happen within that week. I am trying to prevent myself from having to manually click each individual cell to add separately.

Resource 1 worked the week of 7/29/19 on all three projects. His/her hours totaled 19 hours for that week across all three of those projects. I want a formula that would provide that total without me having to make a formula individually clicking on each box. (Example of what I DO NOT want: ='Project 1'!H3+'Project 2'!K3+'Project 3'!B3) I want a formula that references the date of the beginning of the work week.

Thanks in advance!!

**KOKOSEK** · 01-31-2019, 12:19 PM

Can you attached file with some data and expected results in cells, please?

**jackiemariee** · 01-31-2019, 01:20 PM

Attached is a mock-up file. The resource tab shows the results that I want to see, but I use a formula that requires me to click on each cell individually. Granted I copied in pasted that, but I would prefer to match it by the date and name as specified in my initial question.