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.

Customer	Gallons	Total Cost
1	26	???
2	22
3	37
1	42
2	33

<tbody>
</tbody>

Customer 1 Tiers
0 - 20	$5 each
> 20	$3 each
Customer 2 Tiers
0 - 10	$5 each
10 - 20	$4 each
> 20	$3 each
Customer 3 Tiers
0 - 10	$7 each
10 - 30	$5 each
> 30	$4 each

<tbody>
</tbody>

 

[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.

Customer	Gallons	Total Cost	Unit cost				0	0 - 20	$5
1	26	204	3				20	> 20	$3
2	22	165	3
3	37	148	4				0	0 - 10	$5
1	42	204	3				10	10-20	$4
2	33	165	3				20	> 20	$3
							0	0 - 10	$7
							10	10-30	$5
							30	> 30	$4

<tbody>
</tbody>

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