Generate a cost from a pricing matrix with various options

[mikeyduncan]

Hi all,

I am trying to create a formula that will allow a user to simply fill in some criteria and a price will generate accordingly.
I have tried to use a SUMIFS formula but have only got as far as doing the basic price without any of the optional extras.

Here is an example of the questions/criteria that will determine the price:
[TABLE="width: 126"]
<tbody>[TR]
[TD="align: right"]Quantity[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]Width[/TD]
[TD="align: right"]15MM[/TD]
[/TR]
[TR]
[TD="align: right"]Both sides?[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]No. of Extra colours?[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Ferrule?[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Lobster?[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Buckle?[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]Price:[/TD]
[TD="align: right"]1200[/TD]
[/TR]
</tbody>[/TABLE]

My plan was that questions such as both sides, ferrule, lobster, and buckle could be filled in with 'y' to indicate it needs to be included in price. For the extra colours it would need to multiply the price for extra colours by the number entered

Here is the data I wish to use:

[TABLE="width: 689"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]QTY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD][/TD]
[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]10MM[/TD]
[TD="align: right"]1.12[/TD]
[TD="align: right"]0.94[/TD]
[TD="align: right"]0.68[/TD]
[TD="align: right"]0.54[/TD]
[TD="align: right"]0.39[/TD]
[TD="align: right"]0.31[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.22[/TD]
[TD="align: right"]0.20[/TD]
[/TR]
[TR]
[TD][/TD]
[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]15MM[/TD]
[TD="align: right"]1.17[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]0.74[/TD]
[TD="align: right"]0.59[/TD]
[TD="align: right"]0.43[/TD]
[TD="align: right"]0.34[/TD]
[TD="align: right"]0.27[/TD]
[TD="align: right"]0.24[/TD]
[TD="align: right"]0.22[/TD]
[/TR]
[TR]
[TD]Size[/TD]
[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]20MM[/TD]
[TD="align: right"]1.29[/TD]
[TD="align: right"]1.02[/TD]
[TD="align: right"]0.81[/TD]
[TD="align: right"]0.62[/TD]
[TD="align: right"]0.45[/TD]
[TD="align: right"]0.35[/TD]
[TD="align: right"]0.28[/TD]
[TD="align: right"]0.26[/TD]
[TD="align: right"]0.24[/TD]
[/TR]
[TR]
[TD][/TD]
[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]25MM[/TD]
[TD][/TD]
[TD="align: right"]1.10[/TD]
[TD="align: right"]0.86[/TD]
[TD="align: right"]0.67[/TD]
[TD="align: right"]0.49[/TD]
[TD="align: right"]0.39[/TD]
[TD="align: right"]0.36[/TD]
[TD="align: right"]0.34[/TD]
[TD="align: right"]0.32[/TD]
[/TR]
[TR]
[TD][/TD]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ferrule[/TD]
[TD="align: right"]0.01[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lobster[/TD]
[TD="align: right"]0.05[/TD]
[TD][/TD]
[TD][/TD]
[TD]Buckle[/TD]
[TD="align: right"]0.08[/TD]
[/TR]
[TR]
[TD][/TD]
[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]Both sides[/TD]
[TD="align: right"]0.03[/TD]
[TD][/TD]
[TD][/TD]
[TD]Extra colour[/TD]
[TD="align: right"]0.01[/TD]
[TD][/TD]
[TD][/TD]
[TD]Set up[/TD]
[TD="align: right"]10.00

[/TD]
[/TR]
</tbody>[/TABLE]
E.g if customer wants 3000, 15MM lanyards with both side print it will be 0.27 + 0.03 x 3000 = £900 + £10 setup

With the SUMIFS formula I have managed to get the base price but cannot work out how to include the optional extras.

I think I may be going about it the wrong way but if it is possible in any way I would greatly appreciate any help/suggestions.

 

[Eric W]

Welcome to the forum.

This could work for you:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
Size	QTY
10MM
15MM
20MM
25MM
Ferrule	Lobster	Buckle
Both sides	Extra colour	Set up

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]Quantity[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]Width[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15MM[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]10000[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]Both sides?[/TD]
[TD="bgcolor: #FAFAFA, align: right"]y[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.12[/TD]
[TD="align: right"]0.94[/TD]
[TD="align: right"]0.68[/TD]
[TD="align: right"]0.54[/TD]
[TD="align: right"]0.39[/TD]
[TD="align: right"]0.31[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.22[/TD]
[TD="align: right"]0.2[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]No. of Extra colours?[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.17[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.74[/TD]
[TD="align: right"]0.59[/TD]
[TD="align: right"]0.43[/TD]
[TD="align: right"]0.34[/TD]
[TD="align: right"]0.27[/TD]
[TD="align: right"]0.24[/TD]
[TD="align: right"]0.22[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]Ferrule?[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.29[/TD]
[TD="align: right"]1.02[/TD]
[TD="align: right"]0.81[/TD]
[TD="align: right"]0.62[/TD]
[TD="align: right"]0.45[/TD]
[TD="align: right"]0.35[/TD]
[TD="align: right"]0.28[/TD]
[TD="align: right"]0.26[/TD]
[TD="align: right"]0.24[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]Lobster?[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1.1[/TD]
[TD="align: right"]0.86[/TD]
[TD="align: right"]0.67[/TD]
[TD="align: right"]0.49[/TD]
[TD="align: right"]0.39[/TD]
[TD="align: right"]0.36[/TD]
[TD="align: right"]0.34[/TD]
[TD="align: right"]0.32[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]Buckle?[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]Price:[/TD]
[TD="align: right"]910[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.05[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.08[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.03[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]=(INDEX(G3:O6,MATCH(B2,F3:F6,0),IFERROR(MATCH(B1,G2:O2),1))+IF(B3="y",G9,0)+B4*K9+IF(B5="y",G8,0)+IF(B6="y",K8,0)+IF(B7="y",O8,0))*B1+O9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the values you want in column B. I suggest a drop down in B2 using F3:F6 as the values. B3, B5, B6, B7 put a "y" in if those are needed. Put a number in B4. The price is given in B8. The price is driven off of the table. If you change the table, the pricing will automatically adjust.

Hope this helps!