Commission per sale

[IanShockey]

Hi Guys,

I'm trying to get the total amount for the commissions each month.
I've already built the dashboard for it.
However, I'm having difficulties combining them.

Thanks in advance to those who can help me out.

 

[Joe4]

I think we need more details.
I am not seeing any indicator or month anywhere.

Do you have a formula that is not working?
If so, please post it.

Also please explain the logic of how you want this calculated.

 

[IanShockey]

I think we need more details.
I am not seeing any indicator or month anywhere.

Do you have a formula that is not working?
If so, please post it.

Also please explain the logic of how you want this calculated.

Hi Joe,

the month is actually quite irrelevant in this scenario.
I need help specifically in calculating the commissions based on the "Product" Column.
So for example if he sold CFA - Gold it would be automatically $200 for the commission.
now the goal is creating a formula to combine all the Products on that column C (PRODUCT)
and the formula should identify if its gold it $200 if its dia its $300 and sum all the commission in total. "Monthly Commission"

 

[Joe4]

So let's look at your example. You have some listings that match, and some that do not match exactly.
In your example, please explain in detail what the total commission would be, by telling us each part that should be included.

 

[IanShockey]

So let's look at your example. You have some listings that match, and some that do not match exactly.
In your example, please explain in detail what the total commission would be, by telling us each part that should be included.

The Products not part of the Commission Tiers are considered Non-Blueprint for example you would see on Column C "PM+Process Library, Process Library"
The total commission would be based on the products that the agent sold. It's classified into four " Non-Blueprint" as the one mentioned above, Blueprint, CFA Gold, and CFA Diamond.
So every time the agent sells regardless of the amount it would be 8% for Non - BluePrint, 20% for Blueprint, CFA Gold would be $200 and CFA Diamond would be $300.

I already have the calculations individually - however I would need the total calculation for everything combined.
here's what I currently have.

=COUNTIF(C55:C70,D7)*200 = CFA GOLD
=COUNTIF(C55:C70,D7)*300 = CFA Diamond
=C*0.08
=C*0.20

- I really need help in combining these formulas into one and getting the total sum of it.

 

[Joe4]

You still not have answered my question.
What is the total commission that the example you posted should return?
I am trying to understand whether "PM + Process BluePrint" should be considered "BluePrint" (because there is a partial match) or "Non - BluePrint" (because it does not match exactly), and whether "Number of Deals" comes into play at all (i.e. for the flat dollar amounts, is it that much PER deal)?

 

[IanShockey]

You still not have answered my question.
What is the total commission that the example you posted should return?
I am trying to understand whether "PM + Process BluePrint" should be considered "BluePrint" (because there is a partial match) or "Non - BluePrint" (because it does not match exactly), and whether "Number of Deals" comes into play at all (i.e. for the flat dollar amounts, is it that much PER deal)?

Hi Joe sorry if I was unable to answer it.
The total Commission for this example should be $1940 and yes the PM + Blueprint should be considered blueprint.and yes it is per deal for the flat dollar amounts

 

[Joe4]

OK, let's say that your data appears to be as shown in your original post is in the range A2:C5.
Then I think this formula should give you the total commision. I spread it over four lines to show you each piece.

=SUMIF(A2:A5,"CFA Diamond",B2:B5)*300
+SUMIF(A2:A5,"CFA Gold",B2:B5)*200
+SUMIF(A2:A5,"*Blueprint*",C2:C5)*0.2
+SUMIFS(C2:C5,A2:A5,"<>CFA Diamond",A2:A5,"<>CFA Gold",A2:A5,"<>*Blueprint*")*0.08

 

[IanShockey]

OK, let's say that your data appears to be as shown in your original post is in the range A2:C5.
Then I think this formula should give you the total commision. I spread it over four lines to show you each piece.

=SUMIF(A2:A5,"CFA Diamond",B2:B5)*300
+SUMIF(A2:A5,"CFA Gold",B2:B5)*200
+SUMIF(A2:A5,"*Blueprint*",C2:C5)*0.2
+SUMIFS(C2:C5,A2:A5,"<>CFA Diamond",A2:A5,"<>CFA Gold",A2:A5,"<>*Blueprint*")*0.08

Tried this one but it returned as 0

 

[Joe4]

It worked for me.

Run this little macro on any blank sheet, and it will create your example and formula and you should see it all working:

Sub PopulateData()

    Range("A1") = "Product"
    Range("A2") = "CFA Diamond"
    Range("A3") = "CFA Gold"
    Range("A4") = "PM + Process Blueprint"
    Range("A5") = "Process Library"
    Range("B1") = "NUMBER OF DEALS"
    Range("B2") = 1
    Range("B3") = 1
    Range("B4") = 2
    Range("B5") = 1
    Range("C1") = "TOTAL AMOUNT OF DEALS"
    Range("C2") = 3000
    Range("C3") = 3000
    Range("C4") = 6000
    Range("C5") = 3000
    Columns("C:C").NumberFormat = "$#,##0"

    Cells.EntireColumn.AutoFit
    
    Range("C7").FormulaR1C1 = _
        "=SUMIF(R[-5]C[-2]:R[-2]C[-2],""CFA Diamond"",R[-5]C[-1]:R[-2]C[-1])*300" & Chr(10) & "+SUMIF(R[-5]C[-2]:R[-2]C[-2],""CFA Gold"",R[-5]C[-1]:R[-2]C[-1])*200" & Chr(10) & "+SUMIF(R[-5]C[-2]:R[-2]C[-2],""*Blueprint*"",R[-5]C:R[-2]C)*0.2" & Chr(10) & "+SUMIFS(R[-5]C:R[-2]C,R[-5]C[-2]:R[-2]C[-2],""<>CFA Diamond"",R[-5]C[-2]:R[-2]C[-2],""<>CFA Gold"",R[-5]C[-2]:R[-2]C[-2],""<>*Blueprint*"")*0.08"

End Sub