# How to calculate tiered pricing in Excel

Charge each tier's slice at its own rate and add them up: `=SUMPRODUCT((E1>A2:A5)*(IF(E1<B2:B5,E1,B2:B5)-A2:A5)*C2:C5)`.

**Excel:** `=SUMPRODUCT((F1>A2:A5)*(IF(F1<B2:B5,F1,B2:B5)-A2:A5)*C2:C5)` (Every current version)

**Google Sheets:** `=ARRAYFORMULA(SUMPRODUCT((F1>A2:A5)*(IF(F1<B2:B5,F1,B2:B5)-A2:A5)*C2:C5))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| From (GB) | To (GB) | Price per GB |  | Usage (GB) | 1350 |
| 0 | 100 | 0.1 |  | Monthly charge |  |
| 100 | 500 | 0.08 |  |  |  |
| 500 | 1000 | 0.06 |  |  |  |
| 1000 | 1000000 | 0.05 |  |  |  |

Formula in F2: `=SUMPRODUCT((F1>A2:A5)*(IF(F1<B2:B5,F1,B2:B5)-A2:A5)*C2:C5)`

Result: $89.50

## How it works

For each tier, `IF(F1A2:A5)` zeroes out tiers the usage never reaches. Multiplying by each rate and adding gives $10 + $32 + $30 + $17.50 = $89.50.

## Other ways

- `=SUMPRODUCT((F1>A2:A5)*(F1-A2:A5)*(C2:C5-VSTACK(0,C2:C4)))`: No "To" column needed: each tier adds its change in rate on all usage above its start. Excel 2024, Microsoft 365 and Google Sheets.

## Common mistakes

- Tiered pricing isn't volume pricing. Charging all 1,350 GB at $0.05 ($67.50) is a different scheme; see the FAQ.
- The last tier needs a "To" larger than any possible usage.
- In Excel 2019 and earlier, confirm with Ctrl+Shift+Enter because of the IF inside SUMPRODUCT.

## Questions

**How do I charge every unit at the rate of the tier reached (volume pricing)?**

Look up one rate and multiply: `=F1*XLOOKUP(F1,A2:A5,C2:C5,,-1)`.

**How do I show the charge per tier?**

In D2, fill down `=MAX(0,MIN($F$1,B2)-A2)*C2`; the column adds up to the total.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/tiered-pricing