SumProduct with multiple tables

[dustyjay16]

I have found out, thanks to you wonderful people, how to use SumProduct formulas. Now I have a new question. What would be the best way to use the sumproduct formula for tiered pricing, if you have several different sets of tiers. For instance what if you have 5 customers and each of them has their own set of tiers. If you had each set of tiers set up on a different tab, how could you then use a formula to calculate the total cost of each transaction based on whichever customer it is. It seems like it would be a vlookup mixed with a sumproduct.[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Customer
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Gallons
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Total Cost
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]26
[/TD]
[TD="class: xl64, bgcolor: transparent"]???
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]37
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]42
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]33
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 128"]
<tbody>[TR]
[TD="colspan: 2"]Customer 1 Tiers
[/TD]
[/TR]
[TR]
[TD]0 - 20
[/TD]
[TD]$5 each
[/TD]
[/TR]
[TR]
[TD]> 20
[/TD]
[TD]$3 each
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Customer 2 Tiers
[/TD]
[/TR]
[TR]
[TD]0 - 10
[/TD]
[TD]$5 each
[/TD]
[/TR]
[TR]
[TD]10 - 20
[/TD]
[TD]$4 each
[/TD]
[/TR]
[TR]
[TD]> 20
[/TD]
[TD]$3 each
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Customer 3 Tiers
[/TD]
[/TR]
[TR]
[TD]0 - 10
[/TD]
[TD]$7 each
[/TD]
[/TR]
[TR]
[TD]10 - 30
[/TD]
[TD]$5 each
[/TD]
[/TR]
[TR]
[TD]> 30
[/TD]
[TD]$4 each
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Create a specific range for each customer (type) on a sheet called Admin.

Customer 1

0 - 10, 0, $7
> 20, 20, $3

Select A1:C2, name the selection Type1.

Follow the same logic of 3-column ranges for Type2 and Type3.

Now go the data sheet, including the procesing area in A:C...

C2, just enter and copy down:

=LOOKUP(B2,INDIRECT("Type"&A2))*B2

 

[dustyjay16]

I do appreciate your response. I see how that could lookup the table that I'm looking for. However, I need the table to look up the formula, and THEN use the cell referenced of total gallons to calculate total dollars based on that number. So I not only need it to find the table, but also to calculate with a sumproduct formula based on the table.

 

[Aladin Akyurek]

I do appreciate your response. I see how that could lookup the table that I'm looking for. However, I need the table to look up the formula, and THEN use the cell referenced of total gallons to calculate total dollars based on that number. So I not only need it to find the table, but also to calculate with a sumproduct formula based on the table.

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Customer
[/TD]
[TD="width: 92, bgcolor: transparent"]Gallons[/TD]
[TD="width: 90, bgcolor: transparent"]Total Cost[/TD]
[TD="width: 64"]Unit cost[/TD]
[TD="width: 30"]
[/TD]
[TD="width: 36"]
[/TD]
[TD="width: 31"]
[/TD]
[TD="width: 64, align: right"]0
[/TD]
[TD="width: 64"]0 - 20[/TD]
[TD="width: 64"]$5[/TD]
[/TR]
[TR]
[TD="width: 83, bgcolor: transparent"]1[/TD]
[TD="width: 92, bgcolor: transparent"]26[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="width: 64, align: right"]20[/TD]
[TD="width: 64"]> 20[/TD]
[TD="width: 64, align: right"]$3[/TD]
[/TR]
[TR]
[TD="width: 83, bgcolor: transparent"]2
[/TD]
[TD="width: 92, bgcolor: transparent"]22[/TD]
[TD="bgcolor: transparent, align: right"]165[/TD]
[TD="align: right"]3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="width: 83, bgcolor: transparent"]3
[/TD]
[TD="width: 92, bgcolor: transparent"]37[/TD]
[TD="bgcolor: transparent, align: right"]148[/TD]
[TD="align: right"]4[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]0[/TD]
[TD="width: 64"]0 - 10[/TD]
[TD="width: 64, align: right"]$5[/TD]
[/TR]
[TR]
[TD="width: 83, bgcolor: transparent"]1[/TD]
[TD="width: 92, bgcolor: transparent"]42[/TD]
[TD="bgcolor: transparent, align: right"]204[/TD]
[TD="align: right"]3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]10[/TD]
[TD="width: 64"]10-20[/TD]
[TD="width: 64, align: right"]$4[/TD]
[/TR]
[TR]
[TD="width: 83, bgcolor: transparent"]2[/TD]
[TD="width: 92, bgcolor: transparent"]33[/TD]
[TD="bgcolor: transparent, align: right"]165[/TD]
[TD="align: right"]3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]20[/TD]
[TD="width: 64"]> 20[/TD]
[TD="width: 64, align: right"]$3[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]0[/TD]
[TD="width: 64"]0 - 10[/TD]
[TD="width: 64, align: right"]$7[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]10[/TD]
[TD="width: 64"]10-30[/TD]
[TD="width: 64, align: right"]$5[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]30[/TD]
[TD="width: 64"]> 30[/TD]
[TD="width: 64, align: right"]$4[/TD]
[/TR]
</tbody>[/TABLE]

C2, copied down:

=SUMPRODUCT(--($A$2:$A$6=A2),$B$2:$B$6,$D$2:$D$6)

D2, copied down:

=LOOKUP(B2,INDIRECT("Type"&A2))

See:

https://dl.dropboxusercontent.com/u/65698317/dustyjay16.xlsx