Sum Multiple Columns if successful criteria are matched in multiple columns

Here is the scenario that I am trying to create a formula to help me in forecasting. I have attached an excel sheet that has sample.

I am trying to create a sheet that would allow me to add multiple columns where I have hours each resource will be working on week by week bases. Right under the number hours for each week for each resource, I have criteria that tells me which project they are working on that particular week.

Data would look something like this:

Week 1 Week 2 Week 3
Project Hours for resouces A 10 5
Project Name D1 D2

Project Hours for resouces B 30 15
Project Name D2 D2

Now I need a table where I can add how many hours are being worked on for each project.
This table would look something like this:

Project Name Hours worked
D1 10
D2 50


Currently, I am getting away with using SUMIFS. However, when I have many resources working on multiple projects concurrently over three years, using SUMIFS + SUMIFS is getting quite a large formula. Also, when I need to add additional projects or resources the formula is hard to maintain over the time.

Can you help me find more efficient or better way to accomplish this?

Hi

How about a UDF?

Please Login or Register  to view this content.

Invoked with
=myfunc($C$5:$Q$8,A1)

HTH

rylo

This Rocks!!!

Thank you very much!