sum a single cell from multiple sheets based on single date (week-ending) from sheets in summary sheet

[AndaMyrrha]

Hi,
i hope everybody is well.

I am wracking my brains trying to sum values from multiple sheets based on date. The workbook has hundreds of sheets with several sheets per date (day) all with the same format, including the summary sheet. I have used index and indirect to sum common data into a summary sheet, but need to summarize the data from a single cell by date. I have tried using the formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!A:A"),C2,INDIRECT("'"&SheetNames"'!B:B")))

This is not working, I am guessing, because I am only looking for information from single cells and not a range. The date ending cell is C2 and the cell that I want to sum is A5. Also, the date ending can be manually changed by a SUMIF formula based on the week number input on the summary sheet. Could someone please lead me in the right direction. Thanks in advance!

 

[XLfriend777]

Hi Andamyrrha - Welcome to the forum. You might try something like the code below that sums the same cell from multiple sheets. Hope that helps you get started.

=SUM(Sheet2:Sheet4!A5)