Index match or Sumif ???

[Ned]

Hi, I need on the following:

The Fee Table (FeeTbl) have price by the 4 categories namely: Enrol,
Adult, Stdnt & Snr and by year for 2008 to 2011. The Main Table
(MainTbl) are number of persons under each categories. The TotFees
column are the sumproduct of thenumbers in the MainTbl x the prices in
the FeeTbl. For example 48 (in 2008) = 0*10+2*15+2*6+1*6 or 55 (in
2009)= 1*15+2*20+0*6+0*6

What would be the best formula that also does not take too much
resource, would be very appreciative.

Regards
Ed

------------------------------------------------------------
MainTbl
2008
2009
Enrol Adult Stdnt Snr TotFees Enrol Adult Stdnt Snr TotFees
0 2 2 1 48 1 2 0 0 55
2 2 1 0 56 0 2 2 1 52
0 1 0 1 21 2 2 1
0 76
2 0 0 2 32 1 1 0
0 35
:
:
:
FeeTbl
2008 2009 2010 2011
Enrol 10 15 15 15
Adult 15 20 20 20
Stdnt 6 6 6 6
Snr 6 6 6 6

 

[Ned]

The Fee Table (FeeTbl) have price by the 4 categories namely: Enrol,
Adult, Stdnt & Snr and by year for 2008 to 2011. The Main Table
(MainTbl) are number of persons under each categories. The TotFees
column are the sumproduct of thenumbers in the MainTbl x the prices
in
the FeeTbl. For example 48 (in 2008) = 0*10+2*15+2*6+1*6 or 55 (in
2009)= 1*15+2*20+0*6+0*6

Hi Here is better presentation of the table hope it comes out the way
I see it. Thanks

MainTbl
2008 2008 2008 2008 2009 2009 2009 2009
Enrol Adult Stdnt Snr TotFees Enrol Adult Stdnt Snr TotFees
0 2 2 1 48 1 2 0 0 55
2 2 1 0 56 0 2 2 1 58
0 1 0 1 21 2 2 1 0 76
2 0 0 2 32 1 1 0 0 35

:
:
FeeTbl
2008 2009 2010 2011
Enrol 10 15 15 15
Adult 15 20 20 20
Stdnt 6 6 6 6
Snr 6 6 6 6

 

[David-Melbourne-Australia]

I've put column and row headings here to help with laying out the formula:

MainTbl
# A B C D E F G H I J
1 2008 2008 2008 2008 2009 2009 2009 2009
2 Enrol Adult Stdnt Snr TotFees Enrol Adult Stdnt Snr TotFees
3 0 2 2 1 48 1 2 0 0 55
4 2 2 1 0 56 0 2 2 1 58
5 0 1 0 1 21 2 2 1 0 76
6 2 0 0 2 32 1 1 0 0 35



FeeTbl
# A B C D E
1 2008 2009 2010 2011
2 Enrol 10 15 15 15
3 Adult 15 20 20 20
4 Stdnt 6 6 6 6
5 Snr 6 6 6 6

The formula in E3 would read:

= A3 * VLOOKUP(A$2,FeeTbl!$A$2:$E$5,A$1-2006,0) + B3 *
VLOOKUP(B$2,FeeTbl!$A$2:$E$5,A$1-2006,0) + C3 *
VLOOKUP(C$2,FeeTbl!$A$2:$E$5,A$1-2006,0) + D3 *
VLOOKUP(D$2,FeeTbl!$A$2:$E$5,A$1-2006,0)

Copy this formula down and also in the cells in column J of MainTbl and it
should work.

Note that in the VLOOKUP, the third parameter I've put is 'A$1-2006'. This
takes the year and subtracts 2006 - So for 2008, 2008 - 2006 = 2, so the
VLOOKUP uses the second column of the FeeTbl spreadsheet which is where the
prices for 2008 are. Make sure that you always have the year in row 1 of the
MainTbl.

Hope this helps.

David