Equation to calculate cost given material prices

I'm trying to figure out an "efficient" (e.g. not a huge long equation) to calculate the cost of an item given the quantity of a specific material and the cost of the material.

I've been supplied the data - I'm just trying to complete the spreadsheet.

The data came in this format:

Tab1 - Materials Needed

Please Login or Register  to view this content.

Tab2 - Cost of Materials

Please Login or Register  to view this content.

So, for example, my cost for the 1st item would be: [# needed of material 1] * [cost of material 1] + [# needed of material 2] * [cost of material 2] + [# needed of material 3] * [cost of material 3].

That's the "workaround" way I have of writing this forumala.. the problem is I'm going to have other sheets like this in the near future with potentially MANY different materials, and to have to write each one out will get out of hand.

The 2nd tab will ALWAYS have the material lists in the same order as the 1st tab (the DB the data is pulled from I think sort on some type of unique identifier in ascending order when outputting the data).

In a way, if I could do some type of array multiplication, it would work (eg if I had 2 arrays, A & B, A1*B1 + A2*B2 + A3*B3 + .... would do what I want).

Any suggestions on how I best write this "summation of products" equation?

Try =MMULT(B1:D1,Tab2!B2:B4)

Cool, that worked!

One quick follow up though... the first time it didn't work, and I realized the problem was that I can't have 'null' values, instead I need 0's.

Is there a way to get around that? Or do I just need to populate all my 'nulls' with 0's (e.g. when no quantity of a specific material is needed, instead of having a '0' in it, it's just a blank cell at the moment).

I think you have to put 0s in. I'm not very familiar with the formula myself, I just tried it after Sumproduct didn't work for me.