A dry-cleaning ticket is a list of garments with a count next to each: three shirts, two pairs of pants, one suit. The total is every count times its price, added up. You can write that as a line-total column and a SUM, or you can let SUMPRODUCT do the multiply-and-add in one cell.
Three shirts at $3.25, two pants at $6.50 and one suit at $16.00 is $9.75 + $13.00 + $16.00 = $38.75. The blouse row has a price but a quantity of 0, so it adds nothing.
The example
One ticket, four price-list rows. The line-total column is shown for checking, but SUMPRODUCT does not need it.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Line total |
| 2 | Shirt (laundered) | $3.25 | 3 | $9.75 |
| 3 | Pants | $6.50 | 2 | $13.00 |
| 4 | Suit (2-piece) | $16.00 | 1 | $16.00 |
| 5 | Blouse | $6.95 | 0 | $0.00 |
| 6 | Ticket total | $38.75 |
The formula
The whole ticket in one cell:
How it works
SUMPRODUCT pairs the two ranges row by row:
- It multiplies
B2*C2,B3*C3,B4*C4andB5*C5— $9.75, $13.00, $16.00 and $0.00. - Then it adds the four products: $38.75.
- The two ranges must be the same size. Add a garment to the price list and extend both ranges together, or use a whole-column Table reference.
- Rows with a 0 quantity contribute nothing, so a full price list can sit on every ticket with only the counted items mattering.
If you prefer the visible line-total column, keep it: =B2*C2 filled down and =SUM(D2:D5) gives the same $38.75 and is easier for a new counter clerk to audit.
Try it: interactive demo
Enter the piece counts for a ticket; the prices are the example list.
Variations
Add a per-piece surcharge
Starch, rush or fragile handling that applies per piece: add the surcharge to every price inside the SUMPRODUCT.
Price list on another sheet
Look each item's price up from a master list, then multiply by the count. XLOOKUP spills the prices, SUMPRODUCT does the rest.
Pitfalls & errors
A text price ("$3.25" typed with the dollar sign as text) makes SUMPRODUCT treat that row as zero with no error. Keep prices numeric and use currency formatting for the dollar sign.
Put the full price list on every ticket template with quantities defaulting to 0. The clerk only types counts, and the total is right every time.
Mismatched range sizes — B2:B5 against C2:C6 — return #VALUE!. When you add a garment, extend both ranges.
Practice workbook
Frequently asked questions
Why SUMPRODUCT instead of a line-total column and SUM?
Can SUMPRODUCT handle three columns, like price, quantity and a discount factor?
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