Screen printing has real fixed costs per order (screen burning, setup) that a small order has to absorb per-shirt and a large order spreads thin. That is why price breaks by quantity tier are standard: the more shirts in one order, the lower the per-shirt price. IFS checks which tier an order's quantity falls into and returns the right per-shirt price in one formula.
18 shirts prices at $12 each ($216 total); 150 shirts drops to the $6 tier ($900 total).
The example
Four orders spanning all four quantity tiers.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Qty | Price/shirt | Total |
| 2 | Little League team | 18 | $12.00 | $216.00 |
| 3 | Church group | 36 | $9.50 | $342.00 |
| 4 | Company picnic | 72 | $7.50 | $540.00 |
| 5 | School spirit wear | 150 | $6.00 | $900.00 |
The formula
The per-shirt price comes from IFS; the total is that price times quantity:
How it works
IFS checks tiers from smallest to largest and stops at the first match:
B2<25is checked first. 18 shirts is under 25, so it stops here and returns $12 per shirt.- If the first test fails,
B2<50is checked next — 36 shirts is under 50 (and already known to be 25 or more), returning $9.50. B2<100catches orders from 50 up to 99 at $7.50 per shirt.TRUEis the catch-all for 100 or more, returning the $6 bulk price. Multiplying that per-shirt price by B2 gives the order total.
Keep the four tier boundaries and prices on a small rates table and reference it with VLOOKUP instead of hard-coding them in IFS, so a price update means editing one table, not the formula.
Try it: interactive demo
Enter the order quantity.
Variations
Look the price up from a rates table instead
An approximate-match VLOOKUP against a sorted quantity-breakpoint table does the identical job and scales to more tiers without lengthening the formula.
Add a per-color setup fee on top of the tiered price
Multi-color designs add a flat screen-setup fee per color that does not scale with quantity — add it after the tiered total, not folded into the per-shirt price.
Pitfalls & errors
IFS returns #N/A if a quantity does not match any condition — always end the chain with a TRUE,... catch-all so a quantity above your highest named tier (or an unusual value like 0) still returns a price instead of an error.
Check the boundary values themselves (24 vs 25, 49 vs 50), not just numbers in the middle of each tier, when testing — that is where an off-by-one in < versus <= actually shows up.
Quote the customer their exact tier and the next breakpoint ("11 more shirts gets you to the $7.50 tier") — it is a natural upsell that the formula structure makes trivial to calculate on the spot.
Practice workbook
Frequently asked questions
How many quantity tiers should a print shop offer?
Does this handle mixed sizes within one order?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class