I am creating a tiered commission spreadsheet where brokers are paid based on sales they make. I have a formula that does the tiered schedule, but it just gives me a total for all sales, and I need to see individual amounts for each sale. Here is an example: A broker gets a check for 10,000. Of that 10,000, they get 60%. Then they receive a check for 20,000. Of that 20,000, they receive 15,000 at the 60% level, and 5,000 at the 65% level (25,000 is the threshold). Each check is entered individually for the brokers, so I want to be able to see that from the 10,000 they received 6000 and from the 20000 they received 12,250. Right now my formula will only tell me that they received a total of 18,250, but I don't know how much of that came from which clients.... Does that make sense?

Here is my schedule:
0-25,000 60%
25001-50000 65%
50001-75000 70%
75001-100000 75%
100001 + 80%

So, for example, this is what I would like to see happen:
10000 6,000
20000 12,250
etc.


This is what is happening for me:
10,000 18,250
20,000


Can anyone help?!