Commission per product sold

[IanShockey]

Commission Tiers
Non - BluePrint	8%
BluePrint	20%
CFA Gold	$200
CFA Diamond	$300

Hi Guys, Can anyone help me create a formula for this structure?

 

[steve the fish]

You may need some sample data and an explanation. Preferably sample data with expected results.

 

[IanShockey]

Hi Steve,

Like for example

He sells them all together
CFA Gold Diamond Blueprint and a non-blueprint.

The calculation should know if he sold gold he should get $300
if he sold blueprint he should get 20% off of it.
and sum it for the month

 

[steve the fish]

Sure i understand that bit but its completely impossible to help unless you give us some sample data. I cant see your spreadsheet to know what the inputs will look like.

 

[IanShockey]

Sure i understand that bit but its completely impossible to help unless you give us some sample data. I cant see your spreadsheet to know what the inputs will look like.

 

[IanShockey]

View attachment 41829

Is this the one steve?

 

[steve the fish]

From what you have there its not possible. How can the machine know what was sold?

 

[Dave Patton]

Try an if formula or Choose
N.B. It is often a good idea to post an example with the forum's tool XL2BB.
The example would show your sheets layout and you could show the expected result.

Review the formula with Excel's Evaluate Formula.

The Match uses exact match.

Commissions 2021.xlsm
	A	B	C	D	E	F	G
1
2
3				Rates
4				Non-BP	BluePrint	CFA Gold	CFA Diamond
5				0.08	0.2	200	300
6
7	Type	Non-BP	BluePrint	CFA Gold	CFA Diamond
8	Sales amount	1,000.00	1,000.00	1,000.00	1,000.00
9	Commission	0.00	200.00	200.00	300.00
2b

Cell Formulas
Range	Formula
B9:E9	B9	=CHOOSE(MATCH(B7,$D$4:$G$4,0),0.08*B2,0.2*B8,200,300)

 

[IanShockey]

What if it's the total of everything with the exemption of having to formulate it per cell?

 

[IanShockey]