Buy a dozen, pay less each — the oldest deal in the bakery case. IF switches the per-unit price the moment the order reaches a dozen, and multiplies out the total.
Cupcakes at $3.75 each drop to $3.00 at a dozen: 12 × $3.00 = $36, while 6 stay at $3.75 for $22.50.
The example
Three orders, each with a quantity, single-each price, and dozen-each price.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Qty | Single ea | Dozen ea | Total |
| 2 | Half dozen | 6 | $3.75 | $3.00 | $22.50 |
| 3 | One dozen | 12 | $3.75 | $3.00 | $36.00 |
| 4 | Two dozen | 24 | $3.75 | $3.00 | $72.00 |
The formula
IF picks the per-unit price, then quantity multiplies it:
How it works
The count decides which each-price applies:
B2>=12tests whether the order reaches a dozen.- IF returns the dozen each-price
D2when it does, else the single each-priceC2. - Multiplying by the quantity
B2gives the order total.
Use >= so exactly twelve gets the dozen price; > would make a customer buy thirteen to earn the break.
Try it: interactive demo
Enter the quantity and both per-unit prices.
Variations
Three price tiers
Add a case price at, say, four dozen.
Price from a break table
Look the per-unit price up from a quantity-break table.
Pitfalls & errors
Use >=12, not >12, or an order of exactly one dozen misses the discount it was promised.
Keep the dozen price above your cost per unit; a price break that dips below cost turns your best sellers into losses.
Practice workbook
Frequently asked questions
Why >= instead of >?
How do I add more price tiers?
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