# Multi -array / -table Price Calculation Formula

1. ## Multi -array / -table Price Calculation Formula ``Please Login or Register  to view this content.`` ``Please Login or Register  to view this content.``
I need a formula that calculates the following:
1. Look up value of letter from the second table (e.g. x=100, y=200, z=300)
2. For a given row, multiply the values (e.g. 100*1*5, 200*3*15, 300*2*10)
3. SUM the values from step 2

...all in one cell for one total calculation.

Thoughts?

Thanks,
JC  Register To Reply

2. ## Re: Multi -array / -table Price Calculation Formula

Removed by JT  Register To Reply

3. ## Re: Multi -array / -table Price Calculation Formula

Thanks, that works! ...but think I made my example too simplistic in the sense the arrays will not always be the same size. If I try to adapt it to this set, I get an error. ``Please Login or Register  to view this content.`` ``Please Login or Register  to view this content.``
Using your same structure, I use the formula (and get #N/A): =SUMPRODUCT((G1:G5=A1:A3)*(H1:H5)*(B1:B3)*(C1:C3))

Different sized arrays unfortunately to use SUMPRODUCT. Apologize for my poor first dataset example.

-JC  Register To Reply

4. ## Re: Multi -array / -table Price Calculation Formula

And what about the first table- unlimited entries?  Register To Reply

5. ## Re: Multi -array / -table Price Calculation Formula

The simple row by row calculation:

=VLOOKUP(\$A1,\$G\$1:\$H\$3,2,)*B1*C1  Register To Reply

6. ## Re: Multi -array / -table Price Calculation Formula Originally Posted by JohnTopley And what about the first table- unlimited entries?
The first table is dynamic on the number of entries, the second table is fixed. The size of the first will never exceed the size of the second (always a subset or complete set). Originally Posted by JohnTopley The simple row by row calculation:

=VLOOKUP(\$A1,\$G\$1:\$H\$3,2,)*B1*C1
Yep, I can do it row by row...but was hoping for a single cell calculation rather than running subtotals along the way.  Register To Reply

7. ## Re: Multi -array / -table Price Calculation Formula

As far as I can judge array formulas- a likely solution - require consistent array sizes (as my attempted but wrong SUMPRODUCT proved).

Some form of lookup is required (VLOOKUP or INDEX/MATCH) but I don't know how, or if, they can be incorporated in the required way.  Register To Reply

8. ## Re: Multi -array / -table Price Calculation Formula Originally Posted by JohnTopley As far as I can judge array formulas- a likely solution - require consistent array sizes (as my attempted but wrong SUMPRODUCT proved).

Some form of lookup is required (VLOOKUP or INDEX/MATCH) but I don't know how, or if, they can be incorporated in the required way.
...was hoping some sort of SUMIFS or other function that could handle it. Thanks again; may have to eventually give up on a single formula. -JC  Register To Reply

9. ## Re: Multi -array / -table Price Calculation Formula

With a "helper column" containing the VLOOKUP e.g in D in the formula below

=SUMPRODUCT((B1:B4)*(C1:C4)*(D1:D4))  Register To Reply