Competition season invoicing is where studios lose money quietly. Every dancer is in a different combination of routines, every routine type has a different fee, and the totals get added up by hand at eleven at night. SUMPRODUCT does the whole thing in one cell and never gets a solo confused with a duo.
Two solos, a duo, three small groups and a large group comes to $575 — and it re-totals the instant the schedule changes.
The example
One fee schedule at the top, then one row per dancer with their entry counts.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Dancer | Solos | Duo/Trio | Small group | Large group | Entry total |
| 2 | Fee each | $135 | $75 | $60 | $50 | |
| 3 | Ava | 2 | 1 | 3 | 1 | $575 |
| 4 | Mia | 0 | 2 | 4 | 2 | $490 |
| 5 | Zoe | 1 | 1 | 2 | 1 | $380 |
The formula
The dollar signs are the whole trick — they lock the fee row so the formula can be dragged down:
How it works
What SUMPRODUCT is doing here:
- It pairs the two ranges cell by cell — solos with the solo fee, duos with the duo fee, and so on — multiplies each pair, then adds the products. That is exactly the invoice, written as one operation.
$B$2:$E$2is absolute so the fee row never drifts when you copy down. This is the single most common thing to get wrong, and it is silent: the totals just come out low for everyone below the first row.B3:E3is relative so each dancer reads their own counts. Both ranges must be the same shape — four cells against four cells — or SUMPRODUCT returns#VALUE!.- Add fee columns by inserting inside the range rather than at the edge. An inserted column between B and E is picked up automatically; one appended after E is not.
Sum the entry-total column for the studio's cheque to the competition, and keep it next to what you have actually collected from families. The gap between those two numbers is the one that matters in February.
Try it: interactive demo
Enter a dancer's entry counts and adjust the fee schedule to match your competition.
Variations
Studio total for the event
Sum the per-dancer column, or run SUMPRODUCT once against the column totals. Both give the same figure; the second is faster to audit.
Entries per dancer
Plain SUM across the count columns gives the routine load, which is the number that predicts costume changes and exhausted eleven-year-olds.
Pitfalls & errors
Group fees are often quoted per dancer, not per routine. Confirm which before you build the schedule row — a $250 large-group fee split eight ways is a very different column than $50 each.
Add a column for the studio's own entry surcharge if you charge one. Keeping it inside the SUMPRODUCT range means it scales with entries automatically instead of being a flat line item you forget to bill.
Mismatched range sizes return #VALUE!. If you add a routine type, extend both the fee row and the count range — extending only one is the classic cause.
Practice workbook
Frequently asked questions
Why SUMPRODUCT instead of four multiplications added together?
Can I use the same sheet for several competitions?
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