On Feb 13, 6:57 am, Regnab wrote:
In a worksheet, I have 2 tables. 1 gives the price for freighting
different sorts of material. The other lists where the material is
needed. EG

DirtA: $100
DirtB: $250
DirtC: $30
DirtD: $80

Site1 DirtB 100kg
Site1 DirtC 270kg
Site2 DirtA 300kg
Site2 DirtB 150kg
Site3 DirtB 120kg

What I need to do is have a summary for each site on how much the
freight will cost for each site. It 'can' be done using sumproduct and
adding each dirt type together, but the problem is that there are 20
different dirt types for each site so the formula is too long.

What I am asking is is there a way to 'combine' the tables in a
formula, where it would multiply the kgs of each dirt type by the
freight cost to provide the overall freight cost for each site. IE
Site 1: (250 x 100kg)+(30 x 270). I could do it easily in Access by
linking the primary key but I'm not quite as up to speed with excel.

Any assistance or tips on where to look or what to search for would be
much appreciated.

Regards

Reg

The first thing I'd recommend you do is use a vlookup between the
tables so that you can get your prices into the second table. You can
find more information about VLOOKUPs he-

http://teachr.blogspot.com/2006/07/v...ermediate.html

Following that a pivot table might be useful to get summary details.
Microsoft have a good link on using Pivot Tables he-

http://www.microsoft.com/dynamics/us...s_collins.mspx

Hope this helps,
Matt Richardson
http://teachr.blogspot.com