Is there an Excel function that sums multiple values each multiplied by a different lookup value?

1
votes

I am setting up an Excel workbook that will take material usages as inputs. I want to be able to multiply these inputs against known material compositions that I have entered in a table.

Currently I have been doing =usage material1*vlookup(material1,material table,index,false)+usage material2*vlookup(material2,material table,index,false).....

enter image description here

Is there an easier way to do this considering I have large sets of material usages? I really want to avoid having the same formula repeated up to 30 times in the same cell just to sum it all up.

I would like a formula that will automatically multiply material usage * looked up composition and sum this formula for all materials input.

2
excelexcel-formulalookup
Please create some mock data and expected Input/output. it will help us understand. – Scott Craner
Sure, say I have materials X, Y, and Z. Each material contains a certain amount of chemical A. So for example, X contains 10% A, Y contains 15% A, and Z contains 20% A. If I know I used 20 gallons of each X, Y, and Z, I would like the formula to lookup the amount of chemical A in each material which is known in a table. Then, I want the formula to sum XA+YA+Z*A. 20 gal * 10% + 20 gal * 15% + 20 gal * 20%. – sfischer61uz
Additional information should be added to the question itself using edit. Also create a tabular layout of the data and expected output. Words are harder to visualize than a table. – Scott Craner
I have attached a screenshot with a quick example of what I have been doing so far. It accomplishes the goal, I just am curious as to whether or not there is any easier way to do this considering I have a lot more than 3 materials I need to calculate for. – sfischer61uz

2 Answers

4
votes

Here's another way...

=SUMPRODUCT(SUMIF(F6:F8,A6:C6,G6:G8),A7:C7)

...which needs to be confirmed with just ENTER.

Hope this helps!

2
votes

Fast but a bit complicated solution:

{=SUM(INDEX(G1:G4,N(IF(1,MATCH(A1:A5000,F1:F4,0))))*B1:B5000)}

Another solution:

{=SUM(LOOKUP(A1:A5000,F1:F4,G1:G4)*B1:B5000)}

As noted in comments: lookup table must be sorted in ascending order. Also, if a match does not exist for a lookup value, it finds an approximate match, which likely is not the desired result.

enter image description here

SPEED TEST WITH 5000 EXECUTIONS OF FORMULA:

{=SUM(LOOKUP(A1:A5000,F1:F4,G1:G4)*B1:B5000)}

5.237412 Seconds Elapsed | 16,325,977 Ticks Elapsed

{=SUM(INDEX(G1:G4,N(IF(1,MATCH(A1:A5000,F1:F4,0))))*B1:B5000)}

7.941025 Seconds Elapsed | 24,753,637 Ticks Elapsed

=SUMPRODUCT(SUMIF(F1:F4,A1:A5000,G1:G4),B1:B5000)

12.338439 Seconds Elapsed | 38,461,185 Ticks Elapsed