How to calculate tiered pricing in Excel

Q: 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).

Q: 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.

Excel formula

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

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).

Result on the example: $89.50 in F2
Works in: Every current version
Google Sheets: Different formula, see below

See it work

The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.

How it works

For each tier, IF(F1<B2:B5,F1,B2:B5)-A2:A5 is how many GB fall inside it: 100, 400, 500 and 350 for 1,350 GB. (F1>A2:A5) zeroes out tiers the usage never reaches. Multiplying by each rate and adding gives $10 + $32 + $30 + $17.50 = $89.50.

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

Result89.5

F11,350
The value in F1.
A2:A54 rows × 1 col: 0, 100, 500, 1,000
A range: 4 cells in one column.
F1>A2:A5{4 rows × 1 col} TRUE, TRUE, TRUE, TRUE
The > operator tests greater than.
B2:B54 rows × 1 col: 100, 500, 1,000, 1,000,000
A range: 4 cells in one column.
F1<B2:B5{4 rows × 1 col} FALSE, FALSE, FALSE, TRUE
The < operator tests less than.
IF(F1<B2:B5,F1,B2:B5){4 rows × 1 col} 100, 500, 1000, 1350
IF: Returns one value if a test is true, another if false.
- logical_testF1<B2:B5{4 rows × 1 col} FALSE, FALSE, FALSE, TRUE
- value_if_trueF11,350
- value_if_falseB2:B54 rows × 1 col: 100, 500, 1,000, 1,000,000
IF(F1<B2:B5,F1,B2:B5)-A2:A5{4 rows × 1 col} 100, 400, 500, 350
The - operator subtracts.
F1>A2:A5)*(IF(F1<B2:B5,F1,B2:B5)-A2:A5{4 rows × 1 col} 100, 400, 500, 350
The * operator multiplies.
C2:C54 rows × 1 col: $0.10, $0.08, $0.06, $0.05
A range: 4 cells in one column.
F1>A2:A5)*(IF(F1<B2:B5,F1,B2:B5)-A2:A5)*C2:C5{4 rows × 1 col} 10, 32, 30, 17.5
The * operator multiplies.
SUMPRODUCT((F1>A2:A5)*(IF(F1<B2:B5,F1,B2:B5)-A2:A5)*C2:C5)89.5
SUMPRODUCT: Multiplies arrays row by row and adds the results.
- array1F1>A2:A5)*(IF(F1<B2:B5,F1,B2:B5)-A2:A5)*C2:C5{4 rows × 1 col} 10, 32, 30, 17.5

In Google Sheets

Google Sheets needs a different formula: =ARRAYFORMULA(SUMPRODUCT((F1>A2:A5)*(IF(F1<B2:B5,F1,B2:B5)-A2:A5)*C2:C5)). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUMPRODUCT((F1>A2:A5)(F1-A2:A5)(C2:C5-VSTACK(0,C2:C4)))	$89.50	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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate tiered pricing

Storage is billed in tiers: each GB is charged at the rate of the tier it falls in. In F2, calculate the charge for the usage in F1.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.