# Help Calculate Bonus

#### callmejoe

##### New Member
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.

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

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Hope this makes sense.

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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

Last edited:
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.

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

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

1,203,236
Messages
6,054,298
Members
444,715
Latest member
GlitchHawk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back