Multiple Sheets, Multiple Criteria

[KdeBoer]

Hi all!

I have a question regarding on how to get multiple values from different sheets with multiple criteria.

What i basically want to do is the following thing:
I have multiple weeks (Week 1-52)
in all these weeks i fill in the hours that i've been busy on a project with a certain sub-task.
In a seperate sheet I want it to be summarized.

I used the following formula:

=SUMPRODUCT(('WEEK1'!$C$4:$C$13='SUMMARYSHEET'!D3)*('WEEK1'!$E$4:$E$13='SUMMARYSHEET'!A5)*('WEEK1'!$H$4:$H$13))

This actually works! It gets information from 1 sheet.
But what do i need to do to get it from the other sheets aswell?

I tried the following thing:
=SUMPRODUCT(('WEEK1'!$C$4:$C$13='SUMMARYSHEET'!D3)*('WEEK1'!$E$4:$E$13='SUMMARYSHEET'!A5)*('WEEK1'!$H$4:$H$13))+SUMPRODUCT(('WEEK2'!$C$4:$C$13='SUMMARYSHEET'!D3)*('WEEK2'!$E$4:$E$13='SUMMARYSHEET'!A5)*('WEEK2'!$H$4:$H$13))

What i now did was making a sum out of two sheets, but the formule will get kinda large when I need to do this for 52 weeks.


Now i have tried the following thing:
=SUMPRODUCT(('WEEK1:WEEK52'!$C$4:$C$13='SUMMARYSHEET'!D3)*('WEEK1:WEEK52'!$E$4:$E$13='SUMMARYSHEET'!A5)*('WEEK1:WEEK52'!$H$4:$H$13))

entering this formule gives me the following thing, and is NOT working:
=SUMPRODUCT(('WEEK1:[WEEK52]WEEK52'!$C$4:$C$13='SUMMARYSHEET'!D3)*('WEEK1:[WEEK52]WEEK52'!$E$4:$E$13='SUMMARYSHEET'!A5)*('WEEK1:[WEEK52]WEEK52'!$H$4:$H$13))


Can anyone help me on this part?


Best Regards,

Klaas

 

[Special-K99]

If all the sheets have the same format you may be able to use a 3D reference which is one formula that effectively specifies a start and end sheet
Google 3D Reference and Excel to a list of examples.
There are restrictions on this type of method though.

 

[KdeBoer]

If all the sheets have the same format you may be able to use a 3D reference which is one formula that effectively specifies a start and end sheet
Google 3D Reference and Excel to a list of examples.
There are restrictions on this type of method though.

I already searched on this part.

The sheets are having the same format. Only different projectnumbers and tasknumbers. There are two criteria which are needed.
I'm now trying it with only 4 sheets as example.


What i want to do.
In the summary page I have listed a ProjectNumber,
I'm searching in the other sheets on that projectnumber. All project have differend kind of task which is also numbered.
In the summery I want to have a total sum of each project and divided over the separated tasks.

For example:

 

[KdeBoer]

https://ibb.co/dFmczm
https://ibb.co/hsasX6

 

[KdeBoer]

https://ibb.co/dFmczm
https://ibb.co/hsasX6

=SOMPRODUCT(SOM.ALS(INDIRECT("'"&Sheets&"'!"&"A2:A5");A3;INDIRECT("'"&Sheets&"'!"&"C2:C5")))

This is for one criteria, how can i add a second criteria?