Excel Formula to Calculate a Bonus When All Sales Targets Are Met
Question details
Calculate an employee bonus only if the employee meets the sales threshold in every assigned category.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sales data is stored in an Excel Table with multiple sales-percentage records per employee ID, and a bonus must be calculated per employee.
- Observed behavior
- The bonus should return zero when any sales percentage for the same employee is below 90%; otherwise, it should calculate the applicable rate multiplied by the percentage.
Make sure the data is formatted as an Excel Table and that the columns are named ID, Sales %, Rate, and %. Confirm that sales percentages are stored as numeric percentages, such as 90% or 0.9.
Use MINIFS to check every sales target before calculating the bonus
Use this method when each employee has multiple category records in an Excel Table and all records must meet the 90% threshold before a bonus is paid.
MINIFS returns the lowest Sales % for the current employee ID. If that lowest value is below 90%, the IF function returns zero. Otherwise, the formula calculates the bonus using the current row's Rate and % values.
Click the first data cell in the Bonus column of your Excel Table.
Type or paste =IF(MINIFS([Sales %],[ID],[@ID])<0.9,0,[@Rate]*[@[%]]) and press Enter.
Excel fills the formula down the Table column automatically. Employees with any Sales % below 90% receive 0; eligible employees receive Rate multiplied by %.
Use a range-based formula when the data is not an Excel Table
Use this option if your employee IDs and sales percentages are in ordinary worksheet ranges rather than structured Table columns.
Calculate All-Target Sales Bonuses in WPS Spreadsheets
WPS Spreadsheets lets you use IF and MINIFS formulas to verify every sales category for an employee before calculating a bonus. Open an Excel workbook in WPS Office, enter the formula in the Bonus column, and apply it across your data.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open your Excel .xlsx or .xls sales workbook.
- 2. Verify the sales data columns: Check that the worksheet includes employee IDs, sales percentages, rates, applicable percentages, and a Bonus column.
- 3. Enter the conditional bonus formula: Select the first Bonus cell and enter =IF(MINIFS([Sales %],[ID],[@ID])<0.9,0,[@Rate]*[@[%]]).
- 4. Apply the formula to all employees: Press Enter, then use the fill handle or Table column fill behavior to apply the formula to the remaining records.
Frequently Asked Questions
Why does the formula use MINIFS instead of SUMIFS?
MINIFS finds the lowest Sales % for each employee ID. Because every category must meet the target, one value below 90% is enough to return a zero bonus.
Does this formula pay a bonus when an employee reaches exactly 90%?
Yes. With <0.9, a value of exactly 90% qualifies. Replace <0.9 with <=0.9 if the rule requires sales to be greater than 90%.
How do I enter the formula in an Excel Table?
Click the first cell under the Bonus column, enter =IF(MINIFS([Sales %],[ID],[@ID])<0.9,0,[@Rate]*[@[%]]), and press Enter. Excel should apply it to the Table column.
What happens if an employee has one category at 85% and other categories above 90%?
MINIFS returns 85%, which is below 90%, so the IF function returns 0 for that employee's bonus.
