Calculate Tiered Sales with an Excel IF Formula
Question details
Calculate 5% of total sales when the tier is platinum and return zero for all other tiers.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A sales worksheet contains tier and total sales fields, and each row needs a calculated result.
- Observed behavior
- The desired result is 5% of total sales for platinum rows and zero for every other tier.
Identify the Tier and Total Sales columns and choose an empty result column. If you use structured references, check that the Excel table headers match those names.
Use structured references in an Excel table
Use this formula when your data is in an Excel table with Tier and Total Sales headers.
In the Excel table, select the first data cell in an empty result column.
Type =IF([@Tier]="platinum",[@[Total Sales]]*0.05,0) and press Enter.
Check whether Excel filled the formula down the table column. If not, copy the formula to the remaining result cells.

Use ordinary cell references for sales data
Use this option when the data is not in a table or you prefer cell references.
Calculate Platinum Tier Sales in WPS Spreadsheet
Use an IF formula in WPS Spreadsheet to calculate 5% of sales for platinum rows and zero for other tiers.
- 1. Open the worksheet: Open WPS Spreadsheet and select Open to load your sales workbook.
- 2. Check the columns: Locate the Tier and Total Sales cells in the first data row. In this example, they are C2 and D2.
- 3. Enter the formula: Select an empty result cell, such as E2, and enter =IF(C2="platinum",D2*0.05,0). Change the references if your columns differ.
- 4. Fill the remaining rows: Press Enter, then drag the result cell's fill handle down the column.

Frequently Asked Questions
What does the @ symbol mean in the table formula?
In an Excel table, @ refers to the value in the current row. For example, [@Tier] reads the Tier value for that row.
Does the formula distinguish between Platinum and platinum?
No. The IF comparison in these formulas is not case-sensitive, so Platinum and platinum both match.
Why might a platinum row return zero?
Check the Tier cell for extra spaces or a different spelling. Also confirm that the formula references the correct Tier column.
How do I change the 5% rate?
Edit the formula in the result cell and replace 0.05 with the desired decimal rate. For example, use 0.08 for 8%, then fill the revised formula down.




