Sum values based on a range of data

[MyStix01]

Hoping to get some help on the spreadsheet I am currently working on.

Basically, I just need a formula in my column D and column G.

Want I wanted is the formula to automatically calculate commission values based on the parameters I set in cells B3 to C5.

Such that it calculates in this way in column D:

First : Compute the commission value of the amount in cell C8 that is less than or equal to $350,000 by the rate in cell D3, (simple stated, compute the first $350,000).
Second : Compute the commission value of the amount equal to or greater than $350,001 up to $500,000 (or actual value if lower than $500,000) by rate in cell D4
Third : Compute the commission value of the amount greater than $500,000 by rate in cell D5.

As for column G,

I would want to get the totals of the commission value per code name but without using column D as reference. Which means the formula in column G has to calculate the values on it’s own.

Note that I would need the formulas to reference the upper cells (B3:D5) as that figures might change from time to time.

Really hoping someone could help me out. Thank you thank you so much! 

[Bo_Ry]

I think you calculation missing 1-2$

D10 =(B4*D3)+((B5-(B4+1))*D4)+((C10-(B5+1))*D5)

if remove commission rate D3:D5
A10 =(B4)+((B5-(B4+1)))+((C10-(B5+1))) = $683,266.00 from $683,268.00

same as D24:D26

E27 =B4+(C25-B25)+(C26-B26) = $683,267.00 from $683,268.00


Please try E8 with Ctrl+Shift+Enter

=SUM((C8>$C$2:$C$4)*(C8-$C$2:$C$4)*IFERROR($D$3:$D$5-$D$2:$D$4,$D$3))

[MyStix01]

Hi Bo,

This is great! I was so worried I couldn’t get this done. Your timing is just perfect. Thank you for helping me out! Have a blessed day ahead. 

[Bo_Ry]

MyStix01,

Happy to help
Thank for the rep and mark topic Solved.