Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Data
I have 2 sheets of data as seen below..
Sheet1: a b c d e f g h 1 FW Date DOW 8100 8101 8102 8103 8104 2 200601 5/30/2005 Monday 321 280 332 194 262 3 200601 5/31/2005 Tuesday 285 354 423 283 318 4 200601 6/1/2005 Wednesday 340 473 385 275 359 5 200601 6/2/2005 Thursday 446 404 411 301 340 Sheet2: a b 1 Restaurant ID Group 2 8100 Group1 3 8101 Group2 4 8102 Group3 5 8103 Group2 6 8104 Group1 I would like to create a formula that can reference FW, Date, DOW and a group to calculate the average number for a certain group by reference both sheets. Here is an example: a b c d 1 FW Date DOW Group2 2 200601 6/1/2005 Wednesday 374 3 Here is what I have so far: =Sumproduct((Sheet1!FWa1:FWa5=a2)*(Sheet1!FWb1:FWb 5=b2)*(Sheet1!FWc1:FWc5=a2)* After that, I know how to reference a specific restaurantbut am not sure how to reference every restaurant associated with a group. Any help would be greatly appreciated. Thanks, Chad |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Data
Sorry, the formula I showed at the bottom had a typo...the last a2 should be
c2 like so: =Sumproduct((Sheet1!FWa1:FWa5=a2)*(Sheet1!FWb1:FWb 5=b2)*(Sheet1!FWc1:FWc5=c2)* "Chad" wrote: I have 2 sheets of data as seen below.. Sheet1: a b c d e f g h 1 FW Date DOW 8100 8101 8102 8103 8104 2 200601 5/30/2005 Monday 321 280 332 194 262 3 200601 5/31/2005 Tuesday 285 354 423 283 318 4 200601 6/1/2005 Wednesday 340 473 385 275 359 5 200601 6/2/2005 Thursday 446 404 411 301 340 Sheet2: a b 1 Restaurant ID Group 2 8100 Group1 3 8101 Group2 4 8102 Group3 5 8103 Group2 6 8104 Group1 I would like to create a formula that can reference FW, Date, DOW and a group to calculate the average number for a certain group by reference both sheets. Here is an example: a b c d 1 FW Date DOW Group2 2 200601 6/1/2005 Wednesday 374 3 Here is what I have so far: =Sumproduct((Sheet1!FWa1:FWa5=a2)*(Sheet1!FWb1:FWb 5=b2)*(Sheet1!FWc1:FWc5=a2)* After that, I know how to reference a specific restaurantbut am not sure how to reference every restaurant associated with a group. Any help would be greatly appreciated. Thanks, Chad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup to Return a Range of Data | Excel Discussion (Misc queries) | |||
Macro question | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Inserting a new line in spreadsheet | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |