Want to use SUMPRODUCT / SUMIF on Multiple Worksheets on Excel

[The Salt]

I have multiple sheets (Sheet A, Sheet B, Sheet C) with label, time, and data i.e

• A1 blank
• B1:E1 labels (ie. TC1, TC2, TC3, TC4)
• A2:A5 times (06:00, 06:01, 06:02, 06:03)
• B2:E5 numerical data

I listed the sheet names on a Summary sheet in cells A1:A3 and the label I am looking for in B1 (i.e. TC1) and time in C1 (i.e. 06:02). I want to look up data based on two criteria. For example, find the data for TC1 at 06:02, looking across the multiple sheets. Note, TC1 would only appear on either Sheet A, Sheet B, or Sheet C. I tried SUMPRODUCT and was able to look up based on a single criteria (i.e. label), but couldn't figure out how to look up based on two criteria. Here's what I tried, hard coding the time 06:02 to the data in row 4 =SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$3&"'!$1:$1" ),B1,INDIRECT("'"&$A$1:$A$3&"'!$4:$4"))). Now how do you expand this to not 'hard code' the time data and have it look it up for you. I have been trying various combinations of sumif, match, and index, and can't figure it out. Any help is greatly appreciated.

[BillieJ]

This is a string "'!$4:$4" so it can be catenated like any othe rstring

"'!$4:$4

"!$"&A1&":$"&A1

or using Row

"!$"&Row(A1)&":$"&Row(A1)

[JustineBB]

I still don't understand how this enables me to look for a particular time located in cell C1 and not have to hard code the row the time data is located in within the formula.