Best Way to Calculate data from two tables in a sheet. VLOOKUP?

[heresteve2]

Hello! I have two tables and I want to be able to use the data to total up quantities and product types into a third table.

I can figure out how to do this manually by having one cell times another plus one cell times another and so on and so forth but seems to me there has to be a simpler formula that I don't know of or am not remembering. I've attached my spreadsheet.

I'm looking for the Cell B21 to calculate the total amount of dollars needed in January for dishwashers. The top table shows the number of units I will need by unit type (in column A) and the second table shows the amount of each of the items by unit type. In this case we would be looking at Column P.

I would greatly appreciate any help anyone can provide!

[63falcondude]

B21 =SUMPRODUCT(($B$11:$P$11="DW")*$B$12:$P$18*B$2:B$8) Drag this through M21.
B23 =SUMPRODUCT(($B$11:$P$11="Microwave")*$B$12:$P$18*B$2:B$8) Drag this through M23.
etc.

Unless you match the names in A21:A32 with the names in B11:P11 somehow, you will have to hard-code the values into the formulas.

[Jonmo1]

Looks like sumproduct will do it..

B21: =SUMPRODUCT(B$2:B$8,$P$12:$P$18)

[heresteve2]

Wow I guess I really over-thought that one!

Thanks!

[Jonmo1]

You're welcome.

Like FalconDude said

If you can make the values in A21:A32 match up exactly with the values in B11:P11, then it would be much easier to formulate which column needs to be used.

Something like this
=SUMPRODUCT(B$2:B$8,INDEX($B$12:$P:18,0,MATCH($A21,$B$11:$P$11,0)))