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

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