Help Calculate Bonus

[callmejoe]

Hoping someone can help me.

I need to create something that can help calculate a "bonus" if certain items are sold on the same invoice with amount greater than $X.

Something like this:

To qualify employee X needs to sell gallon of milk to qualify priced at least $X, if he can add yogurt and eggs that are at least priced $X or more on the same invoice the employee gets $X.

[TABLE="width: 415"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Invoice[/TD]
[TD]Employee[/TD]
[TD]SKU[/TD]
[TD]Description[/TD]
[TD] Amount [/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD="align: right"]111 [/TD]
[TD]Jeff[/TD]
[TD]A5B355[/TD]
[TD]Hillshire 12[/TD]
[TD] $ 5.00[/TD]
[TD]Eggs[/TD]
[/TR]
[TR]
[TD="align: right"]111 [/TD]
[TD]Jeff[/TD]
[TD]190BDR[/TD]
[TD]Gallon[/TD]
[TD] $ 2.00[/TD]
[TD]Milk[/TD]
[/TR]
[TR]
[TD="align: right"]111 [/TD]
[TD]Jeff[/TD]
[TD]293DKS[/TD]
[TD]32oz Vanilla[/TD]
[TD] $ 3.00[/TD]
[TD]Yogurt[/TD]
[/TR]
[TR]
[TD="align: right"]444 [/TD]
[TD]Carl[/TD]
[TD]98SKOS[/TD]
[TD]Sharp 4oz[/TD]
[TD] $ 2.00[/TD]
[TD]Cheese[/TD]
[/TR]
[TR]
[TD="align: right"]444 [/TD]
[TD]Carl[/TD]
[TD]FBN398[/TD]
[TD]Chocolate [/TD]
[TD] $ 4.00[/TD]
[TD]Cookies[/TD]
[/TR]
</tbody>[/TABLE]

Hope this makes sense.

 

[sergioMabres]

Hi Joe,
You could try a formula like =IF(AND(F2="milk",SUMIFS(E:E,A:A,A2,F:F,"Eggs")>3,SUMIFS(E:E,A:A,A2,F:F,"Yogurt")>2),"Bonus","") in G2

Invoice	Employee	SKU	Description	Amount	Category	Bonus
111	Jeff	A5B355	Hillshire 12	$5.00	Eggs
111	Jeff	190BDR	Gallon	$2.00	Milk	Bonus
111	Jeff	293DKS	32oz Vanilla	$3.00	Yogurt
444	Carl	98SKOS	Sharp 4oz	$2.00	Cheese
444	Carl	FBN398	Chocolate	$4.00	Cookies

<tbody>
</tbody>

In the formula the Bonus should be replace with the bonus amount, in this case the bonus is in the first condition, the Milk row

Or you could have a formula like this =IF(AND(SUMIFS(E:E,A:A,A2,F:F,"Milk")>1,SUMIFS(E:E,A:A,A2,F:F,"Eggs")>3,SUMIFS(E:E,A:A,A2,F:F,"Yogurt")>2),"Bonus","") in G2
Here the Bonus is in every row of the invoice, the Bonus has to be replace by the Bonus amount calculation

Invoice	Employee	SKU	Description	Amount	Category	Bonus
111	Jeff	A5B355	Hillshire 12	$5.00	Eggs	Bonus
111	Jeff	190BDR	Gallon	$2.00	Milk	Bonus
111	Jeff	293DKS	32oz Vanilla	$3.00	Yogurt	Bonus
444	Carl	98SKOS	Sharp 4oz	$2.00	Cheese
444	Carl	FBN398	Chocolate	$4.00	Cookies

<tbody>
</tbody>

The values >1 >3 and >2 are the $ values that has to be surpassed (at least priced $X)

The problem is that this formula will increase rapidly as you add more Bonus rules

You could create a UDF formula with many, many bonus rules, but that will take a couple hours to program

Cheers
Sergio

 

[Fluff]

Cross posted https://www.excelforum.com/excel-general/1262479-help-calculate-bonus.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[sergioMabres]

Hi Fluff what shall I do move my answer to the other post? Or erase it?
Sergio

 

[Fluff]

Hello Sergio
You don't need to do anything.
The message was aimed at the OP, who will hopefully respond & let you know if your suggestion worked