How to use INDIRECT with a SUMIF formula for a date range

[ToucheSir]

I'm trying to get a SUMIFS formula to dynamically work with multiple tabs in an excel workbook. I have numerous tabs in this workbook, each that contains client data I update daily. In the example below I have a client called "Joes 27" and on that sheet there is sales data listed daily. I am looking to have a summary page where I can enter a date range and have it sum up the sales data for that range for Joes 27. And then for me to be able to switch it to another client name. I use this currently:

=SUMIFS('Joes 27'!5:5, 'Joes 27'!2:2, ">="&B1, 'Joes 27'!2:2, "<="&C1)

"Joes 27" is the tab name of the client tab. Others are called "Citi 15" for example.
Row 5 in each client tab is what I want added up.
Row 2 in each client tab is the list of dates. (6/1, 6/2 , 6/3)
On the summary page, I enter the date range in B1 and C1 (6/1 6/3)

What formula can I use so I can enter a client name on the summary page and have it use the SUMIFS formula to add up the data in that range on that page. I assume INDIRECT function but I can't seem to figure it out.

I sincerely appreciate the help!

 

[C Moore]

I use indirect to reference tab names from a cell value fairly often, =INDIRECT("'"&CellwithName&"'!5:5") should be the first term above in your example. That is open parentheses, double quote, single quote, double quote ampersand, etc...