Sum cell across multiple sheets depending on multiple criteria

[ExcelHelp79]

Please see the attached file. I have a summary tab that manually sums across all of the sheets and a range data tab that allows for the ranges of sheets and drop down menus. What I'm trying to accomplish is for each cell to sum the corresponding cell across all sheets that have the matching data. For example, we would like to pull the sum of all data fields for all Sundays where Butler and then Gionnico were supervising. I've tried a variety of SUMIFS and SUMPRODUCT statements but can only seem to get them to work on one of the criteria. Please help!

[JohnTopley]

TRY

=IFERROR(SUMPRODUCT(SUMIFS((INDIRECT("'"&Sheets&"'!B6")),INDIRECT("'"&Sheets&"'!B1"),$B$1,INDIRECT("'"&Sheets&"'!E1"),$E$1:$F$1)),"")

NOTE: I unmerged the Supervisor cells in SUMMARY so there is a contiguous range

It also checks only E1 in the "Day" sheets

Also note you could do your summations using:

=SUM('Day 1:Day 31'!I16)

[ExcelHelp79]

Thank you. We're definitely making progress. It appears your formula doesn't calculate in the second supervisor field. Could this be because these fields are merged in the Day tabs? Also, when I copy and paste to my January data file, all fields come up blank. If I remove the IFERROR, I get a #NAME? error.

[JohnTopley]

You are correct that it only counts the FIRST supervisor in the "Day" sheets BUT it does count if there are 3 supervisors selected in SUMMARY and each appears in E1 in the "Day" sheets. I am sure there will be a way to match more than one but I could not get it to work. Hopefully one of the gurus will look at this.

Have you the named range "Sheets" in your January file? I cannot think why else you would get a #NAME error.

[JohnTopley]

Further testing shows it only works on a single cell i.e. E1 not across several as I previously stated.

=IFERROR(SUMPRODUCT(SUMIFS((INDIRECT("'"&Sheets&"'!B6")),INDIRECT("'"&Sheets&"'!B1"),$B$1,INDIRECT("'"&Sheets&"'!E1"),$E$1)),"")

And the reason is: ranges in SUMIFS must be the same number of rows and columns .