Credit for the rep who only owns part of the deal (SUMPRODUCT)
The problem
Problem statement
Deals are often shared: the owner takes the credit percentage in D2:D6, and the rest goes to whoever sourced it. Bookings, owner and credit share are on each row of A2:D6. Put the total credited bookings for the rep in F2 in the Credit cell G2.
Credit is bookings × share, row by row — not the rep's bookings total times an average share.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | F | G | |
|---|---|---|---|---|---|---|
| 1 | Deal | Bookings | Owner | Credit % | Rep | Credit |
| 2 | D-501 | 10000 | Ada | 0.7 | Ada | |
| 3 | D-502 | 20000 | Ben | 0.7 | ||
| 4 | D-503 | 15000 | Ada | 1 | ||
| 5 | D-504 | 5000 | Ada | 0.5 | ||
| 6 | D-505 | 8000 | Ben | 0.7 |
Notes
A split credit is a weighted total, so SUMPRODUCT is the natural tool: the owner test zeroes the rows that are not theirs, and each surviving row contributes bookings × share. Multiplying the rep's total bookings by an average percentage gives a different — and wrong — number.
How to approach
Your answer goes in G2.
- Target cell is G2
- Build the owner test as an array: C2:C6 = F2
- Multiply by bookings and by the credit share
- Shape: =SUMPRODUCT((?=?)*?*?)
FAQ
How do I calculate split credit across two reps?
=SUMPRODUCT((owner_range=rep)*bookings*share). The owner test filters rows, and each row contributes its own bookings × share.
Why is my total wrong when I multiply bookings by the average share?
Because shares differ per row. A 70% deal and a 100% deal do not average out to the same credit as multiplying each row and adding up.