Weighted average w/ multiple text criteria excel 2010

hi

i'm looking to get a weighted average from a table (in 'database format') for specific rows. i'm working on excel 2010.
below might help to convey the idea of what i am trying to achieve, although the actual solution will probably differ considerably.

to illustrate my problem better, below some sample data:

(desk) (type) (commodity) (MT) (cost$) (MTM$)
paris positions corn 1000 $300 $250
paris positionsl wheat 2000 $280 $300
london futures corn -3000 $290 $250
london options wheat -2000 $280 $230
paris futures corn 5000 $280 $250

i need the weighted average cost$ and marked-to-market (MTM$) for each desk and commodity, i.e. weighted average cost$ for corn in paris --> (1000x$300+5000x$250/6000)

this is my formula so far, which returns a DIV/0 error:
=SUMPRODUCT((Table1[Type]="Positions")*(Table1[Type]="Futures")*(Table1[Type]="Options")*(Table1[Desk]='Grain Positions'!$A$3)*(Table1[Commodity]='Grain Positions'!A4);ABS(Table1[MT]);Table1[USD /MT (Cost)])/SUMPRODUCT((Table1[Type]="Positions")*(Table1[Type]="Futures")*(Table1[Type]="Options")*(Table1[Desk]='Grain Positions'!$A$3)*(Table1[Commodity]='Grain Positions'!A4);ABS(Table1[MT]))

thanks for your help!

You can simplify both formulae by using selecting the whole table, and define names by Create From Selection. The formulae then become:

Formula:

Please Login or Register  to view this content.

And

Formula:

Please Login or Register  to view this content.

The formulae are the same but without the difficult to read "Table syntax" that some may find difficult to understand.