Formula Errors

Calculate Tiered Payouts by Quantity With an Excel IF Formula

Jason MillerJason Miller Nov 18, 2026 868 views

Question details

Calculate a payout based on the quantity sold using defined quantity tiers.

Calculate Tiered Payouts by Quantity With an Excel IF Formula
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.
Before you start

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.

Solution 1Recommended

Use a Nested IF Formula for the Payout Tiers

Use this formula to return a fixed payout for each quantity tier.

1
Enter the formula

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.

2
Check the results

Enter sample quantities in B2 to verify the thresholds: 2 returns 0, 3 and 5 return 10, and 6 returns 25.

3
Fill the formula down

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 Nested IF Formula for the Payout Tiers
Fixed payout amounts: The formula returns one payout amount per row; it does not multiply the payout by the quantity sold.
Calculate payouts in WPS

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. 1. Open your worksheet: Open WPS Spreadsheets, then use Open to select your workbook.
  2. 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. 3. Fill the remaining rows: Drag the fill handle from C2 down alongside the quantity values.
  4. 4. Save the workbook: Choose File > Save As and select the Excel XLSX format if you need an Excel-compatible workbook.
Calculate payouts for each quantity tier with a nested IF formulaOpen and save Microsoft Excel XLSX filesFill formulas down multiple rowsUse familiar spreadsheet controls in a free office suite
microsoft office alternative - wps office

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.