# 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

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

Removed by JT

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

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

And what about the first table- unlimited entries?

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

The simple row by row calculation:

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

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.

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.

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

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))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1