Function Problems

Calculate Tiered Sales with an Excel IF Formula

Jason MillerJason Miller Nov 18, 2026 868 views

Question details

Calculate 5% of total sales when the tier is platinum and return zero for all other tiers.

Calculate Tiered Sales with an Excel IF Formula
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.
Before you start

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.

Solution 1Recommended

Use structured references in an Excel table

Use this formula when your data is in an Excel table with Tier and Total Sales headers.

1
Select a result cell

In the Excel table, select the first data cell in an empty result column.

2
Enter the IF formula

Type =IF([@Tier]="platinum",[@[Total Sales]]*0.05,0) and press Enter.

3
Apply it to other rows

Check whether Excel filled the formula down the table column. If not, copy the formula to the remaining result cells.

Use structured references in an Excel table
Match your headers: Replace Tier and Total Sales in the formula if your table uses different column names. The @ symbol refers to the current row.
Calculate sales in WPS Spreadsheet

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. 1. Open the worksheet: Open WPS Spreadsheet and select Open to load your sales workbook.
  2. 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. 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. 4. Fill the remaining rows: Press Enter, then drag the result cell's fill handle down the column.
Calculate the platinum tier result with an IF formulaOpen and edit Microsoft Excel XLSX filesFill the formula down for multiple sales records
microsoft office alternative - wps office

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.