Sum based on 3 criteria with 1 criteria range spanning over multiple columns

Hi,

I need to sum the hours of a column based on multiple criteria. I tried a SUMIFS function but the problem is that the criteria range for one of the criterion spans over more than one column. The information is imported through a query so therefore it's in a different sheet. See attached file.

So based on an input cell "Enter project nb" I want it to summarise all the hours based on 1) the project nb 2) the resource 3) Sprint week nb.
When a project nb belongs to more than one sprint week, the export automatically divides it as can be seen in the file.

Not sure how to proceed

Thanks! I've adjusted it now Attached it in the post.

With an extra column and this sumifs formula it would fix your problem (I hope)

Please Login or Register  to view this content.

Sheet1

C6=SUMPRODUCT((Sheet2!$B$3:$B$7=Sheet1!$B$2)*(Sheet2!$E$3:$E$7=Sheet1!$B6)*(Sheet2!$F$3:$H$7=Sheet1!C$5)*(Sheet2!$D$3:$D$7))