SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria
 

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!

SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria
 

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)



"kazoo" wrote:

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!

SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criter
 

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.

"Joel" wrote:

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)



"kazoo" wrote:

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!