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

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

And what about the first table- unlimited entries?

The simple row by row calculation:

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

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.

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.

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

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

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