Need help Sumifs on different sheet with criteria

[thrilosgate7]

Hello everybody,
first off all it is really nice to be a part of your community as a newbie in excel.
I need some if possible for my excel workbook i have made and it drives me crazy
I wont to count the working Hours off each member(named as Manpower) on different sheet.
I maked a list of memmbers and i want to tell excel that ineed for each name on different sheets as Date
to give me the summ off working Hours. I try to attach the testfile. For The first sheet it seems to work but how can i
tell excel if i want for example anothe date to count with the same criteria as sheet1(07.10)?
a littele help would be fantastic and i hope somebody answers.Sorry for my bad english by the way

[bebo021999]

Using SUMPRODUCT(SUMIF(INDIRECT... to do this.
G2:G3 contains list of sheet names. it can be expanded accross multi dates.
In B2:

Please Login or Register  to view this content.

Drag down.

[thrilosgate7]

Thanks for your quick answer but it doesnt work.
I think i didnt explain correctly.
Let me try.
In the Workbook i have 2 sheets and it is for 2 different days.
I want that on the sheet (Mitarbeiter)it means Employee in row B to summ the ours from the different sheet in row j.
So if i select a name from the dropdown in k excel has to recognize the name and summ the ours in sheet mitrabeiter in row B for each employee.
I dont now how to explain correctly because may englisch is not so well.
Thx for your time and your help.

[JohnTopley]

I don't understand your requirement: how can it sum the hours in "Mitrabeiter" as there is only one entry shown for a given "Manpower"?

If you select a name in either of the 2 sheets, you will get the same result (?) from "Mitrabeiter".

You need to show some example results

Bebo's answer makes more sense to me: SUM all the sheets in "Mitrabeiter" for each name

[thrilosgate7]

thx for your answer but how can i sum all he sheets in Mitarbeiter for each name?
I have tried the formula from bebo but it wont work.
Thx in Advance!!

[JohnTopley]

What does not work mean as it works in the file posted by Bebo? Do you get an error message?

Perhaps change commas "," in the formula to semi-colons ";" .

[thrilosgate7]

I dont understand what this means : G2:G3 contains list of sheet names. it can be expanded accross multi dates.

[JohnTopley]

G2:G3 are your sheet names which are used to retrieve data from each sheet in your workbook. As you add more sheets, you add the sheet names in G4 down and change the range in the formula below which SUMs all sheets

=IF(A2="","",SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$3&"'!$K$4:$K$12"),"*"&$A2&"*",INDIRECT("'"&$G$2:$G$3&"'!$j$4:$j$12"))))

Alternative is to have a named range for sheet names: I called it "Sheets"

=IF(A2="","",SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!$K$4:$K$12"),"*"&$A2&"*",INDIRECT("'"&Sheets&"'!$j$4:$j$12"))))

[thrilosgate7]

Thank you so much bith off you it works.
And it is for me an extra kick to learn excel.
Thanks all for your time!!

[JohnTopley]

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.