Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT with Multiple Criteria | Excel Worksheet Functions | |||
select multiple criteria multiple worksheets | Excel Worksheet Functions | |||
Vlookup for multiple criteria, multiple worksheets | Excel Worksheet Functions | |||
SUMif or SUMproduct across multiple worksheets? | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |