Calculate Tiered Payouts by Quantity With an Excel IF Formula
Question details
Calculate a payout based on the quantity sold using defined quantity tiers.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains quantities sold, and each row needs a payout based on its quantity.
- Observed behavior
- The desired payout is 0 for fewer than 3 items, 10 for 3 through 5 items, and 25 for 6 or more items.
Check which column contains the quantities sold and choose an empty column for the payouts. The examples assume numeric quantities start in cell B2 and the payout formula is entered in C2.
Use a Nested IF Formula for the Payout Tiers
Use this formula to return a fixed payout for each quantity tier.
Select cell C2 and enter =IF(B2<3,0,IF(B2<6,10,25)). Replace B2 with the quantity cell if your worksheet uses a different column.
Enter sample quantities in B2 to verify the thresholds: 2 returns 0, 3 and 5 return 10, and 6 returns 25.
Select C2 and drag the fill handle at its lower-right corner down the payout column to calculate results for the remaining rows.

Use a Shorter Formula With Threshold Tests
Use this alternative if you prefer a compact formula that adds amounts when each threshold is reached.
Calculate Quantity-Based Payouts in WPS Spreadsheets
Use the same nested IF formula in WPS Spreadsheets to calculate fixed payouts and fill the formula down your worksheet.
- 1. Open your worksheet: Open WPS Spreadsheets, then use Open to select your workbook.
- 2. Enter the payout formula: Select C2 and enter =IF(B2<3,0,IF(B2<6,10,25)), adjusting B2 if the quantity is in another cell.
- 3. Fill the remaining rows: Drag the fill handle from C2 down alongside the quantity values.
- 4. Save the workbook: Choose File > Save As and select the Excel XLSX format if you need an Excel-compatible workbook.

Frequently Asked Questions
What payout does the formula return for exactly 3 or 6 items?
The formula returns 10 for exactly 3 items and 25 for exactly 6 items. Enter those quantities in B2 to test the boundaries.
How do I change the payout amounts?
In =IF(B2<3,0,IF(B2<6,10,25)), replace 0, 10, and 25 with the amounts you want for the three tiers.
How can I use the formula for every salesperson?
Enter the formula in C2, then drag the fill handle down the column. Excel adjusts the quantity reference for each row.
Does the formula calculate a payout per item?
No. It returns a fixed amount based on the quantity tier. A per-item payout would require a different formula.




