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:

Quantity	5000
Width	15MM
Both sides?
No. of Extra colours?
Ferrule?
Lobster?
Buckle?
Price:	1200

<tbody>
</tbody>

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:

					QTY
		50	100	200	400	500	1000	3000	5000	10000
	10MM	1.12	0.94	0.68	0.54	0.39	0.31	0.25	0.22	0.20
	15MM	1.17	1.00	0.74	0.59	0.43	0.34	0.27	0.24	0.22
Size
	20MM	1.29	1.02	0.81	0.62	0.45	0.35	0.28	0.26	0.24
	25MM		1.10	0.86	0.67	0.49	0.39	0.36	0.34	0.32
	Ferrule	0.01			Lobster	0.05			Buckle	0.08
	Both sides	0.03			Extra colour	0.01			Set up	10.00

<tbody>
</tbody>

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
1	Quantity	3000				Size					QTY
2	Width	15MM					50	100	200	400	500	1000	3000	5000	10000
3	Both sides?	y				10MM	1.12	0.94	0.68	0.54	0.39	0.31	0.25	0.22	0.2
4	No. of Extra colours?					15MM	1.17	1	0.74	0.59	0.43	0.34	0.27	0.24	0.22
5	Ferrule?					20MM	1.29	1.02	0.81	0.62	0.45	0.35	0.28	0.26	0.24
6	Lobster?					25MM		1.1	0.86	0.67	0.49	0.39	0.36	0.34	0.32
7	Buckle?
8	Price:	910				Ferrule	0.01			Lobster	0.05			Buckle	0.08
9						Both sides	0.03			Extra colour	0.01			Set up	10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula B8 =(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 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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!