Formula for Commission Calculation

[NonStopLeo]

Hi Friends

I am trying to create a formula for commission calculation but I don't know how to do it.

Kindly help me to do it.

I have attached an example sheet.
Commission is applicable if Target of 3 active sales is achieved.
I have two Types of sales i.e Regular or New (Both are counted in target calculation)
But Commission is Applicable on new Sales Only
Commission is paid only if target is achieved.
Commission rate is $500 per sales.
If any sales is cancelled, it will not be included in Target calculation or commission calculation.

Can any one please help me to figure out any formula.

It will be of great help for me.

Thanks

[Bo_Ry]

Please try
F6
=(C6&D6="ActiveNew")*500

G6 I'm not sure about logic but result seem ok.

=(COUNTIF(C4:C6,"active")=3)*(G5&G4="00")*1000+COUNT(SEARCH("Cancelled",C4&C5))*500

[Joske920]

Hi NonStopLeo,

E column should get =IF(C6="Active",IF(D6="New","Yes","No"),"No")
F column should get =IF(E6="Yes",500,0)
G column should get =IF($B6=$B5,"",IF(COUNTIFS($B$6:$B$18,$B6,$E$6:$E$18,"Yes")>=3,SUMIFS($F$6:$F$18,$B$6:$B$18,$B$6;$E$6:$E$18,"Yes"),0))

That should make it apply to minimum 3 sales then pay out for the New only
This is your excel modified
https://1drv.ms/x/s!Aq2RGsLiArA99nZILwp8rO8kEE5D

Grtz

[NonStopLeo]

Please try
F6
=(C6&D6&E6="ActiveNewYes")*500

G6 I'm not sure about logic but result seem ok.

=(COUNTIF(C4:C6,"active")=3)*(G5&G4="00")*1000+COUNT(SEARCH("Cancelled",C4&C5))*500

Hi NonStopLeo,

E column should get =IF(C6="Active",IF(D6="New","Yes","No"),"No")
F column should get =IF(E6="Yes",500,0)
G column should get =IF($B6=$B5,"",IF(COUNTIFS($B$6:$B$18,$B6,$E$6:$E$18,"Yes")>=3,COUNTIFS($F$6:$F$18,$B$6:$B$18,$B$6;$E$6:$E$18,"Yes"),0))

That should make it apply to minimum 3 sales then pay out for the New only
This is your excel modified
https://1drv.ms/x/s!Aq2RGsLiArA99nZILwp8rO8kEE5D

Grtz

HI
I have tried your solution, but it is not giving the desired results, I have entered 1 column with actual results and 1 column with formula results.
Can you please check and advice what error I am making

[jason.b75]

In your first sample file, all of the dates are shown as the 1st day of the month, is that true to your real file, or will it contain actual sale dates anywhere in the month?

E6:- =IF(AND(C6="Active",D6="New"),"Yes","No")
F6:- =IF(E6="Yes",500,)
G6:- =IF(C6="Cancelled",0,CHOOSE(SIGN(COUNTIFS(B$6:B6,">"&EOMONTH(B6,-1),B$6:B6,"<="&EOMONTH(B6,0),C$6:C6,"<>Cancelled")-3)+2,0,SUM(F$6:F6)-SUM(G$5:G5),F6))

This is allowing for any date, the G6 formula can be simplified if your real file dates are consistent with your sample file.

[NonStopLeo]

In your first sample file, all of the dates are shown as the 1st day of the month, is that true to your real file, or will it contain actual sale dates anywhere in the month?

It will be actual date of sales, but calculation and payment of commission will be based on month, so in sample I kept as first date of the month.

[Glenn Kennedy]

E7 and E8.... yes on one sheet and no on the other. Which is correct?

Why is H9 1000, when there are only 2 NEW active sales up to that point?

[jason.b75]

Please stop quoting whole posts when you reply, as you can see from the other replies, it is possible to reply without quoting.

Try the formulas that I've added to my previous post.

[NonStopLeo]

Glenn Kennedy
E column is based on If = C & D are Active & New, then Yes, Otherwise No. Second sheet is correct, sorry for my mistake.
Upto H9, Sales Target of 3 Actives Sales in a month have been achieved so No of applicable sales x 500 = 1000

[NonStopLeo]

Jason.b75
Your formula is perfectly working and giving desired results. I will apply this formula on my actual sheet and will come back to you if any error.

Thank you very much for this great help
It solved my big problem, I was working on this formula since 3 days.

[Joske920]

Hi NonStopLeo,

You shouldnt have changes the title of the date column

I made a translation fault in the first posting it should be sumifs instead of countifs
im going from english to dutch back to english so im sorry it didnt work from the first time

Grtz

[Joske920]

[Glenn Kennedy]

Another possibility:

=N(IF(IF(C6<>"Active",0,SUMPRODUCT(($C$6:C6="Active")*(MONTH($B$6:B6)=MONTH(B6))))>=3,MAX(0,SUM($F$6:F6),0)-SUM($G$5:G5)))

[Bo_Ry]

Please try

F6
=(C6&D6="ActiveNew")*500

G6
=(COUNTIFS(B$6:B6,">"&EOMONTH(B6,-1),C$6:C6,"active")>2)*(SUM(F$6:F6)-SUM(G$5:G5))

[NonStopLeo]

Hi All Guys

Thanks a lot for your quick help as usual. so nice of you.

Problem is solved.

See you soon with new problem

[Glenn Kennedy]

As a matter of interst, which one(s) worked for you?

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[NonStopLeo]

Glenn Kennedy
Solution given in post # 5 & 13 are actually working.
In excitement, I forgot to give reputation which now has been added.
Thanks

[samba_ravi]

G6=IF(AND(C6="Active",D6="New"),CHOOSE(LOOKUP(2,1/(SUM(COUNTIFS($C$6:$C6,"Active",$D$6:$D6,{"New","Regular"},$B$6:$B6,">"&EOMONTH($B6,-1),$B$6:$B6,"<="&EOMONTH($B6,0)))>={0,3,4}),{1,2,3}),0,COUNTIFS($C$6:$C6,"Active",$D$6:$D6,"New",$B$6:$B6,">"&EOMONTH($B6,-1),$B$6:$B6,"<="&EOMONTH($B6,0))*500,500),0)

Please Login or Register  to view this content.

Try this and copy towards down

[samba_ravi]

As per my observation if in any month there are less then 3 active statues with 1 or 2 Type "New" and Status "Active" then the formulas giving wrong results

[jason.b75]

As per my observation if in any month there are less then 3 active statues then the formulas giving wrong results

I assume you're referring to your own formula there, Samba.

From what I can see, mine gives the correct result in that case, as does Bo_Ry's from post #14, yours doesn't! (I haven't checked the others)

[samba_ravi]

Your are right jason there is a flaw in my post, here is the modified one
G6=

Please Login or Register  to view this content.

[samba_ravi]

I assume you're referring to your own formula there, Samba.

See the attached file, here is what I tried to say in post no 19

[jason.b75]

Now I see, the month with 1 or 2 qualifying sales is correct but everything following it is wrong.

This one, using Bo_Ry's format, looks better.

=(COUNTIFS(B$6:B6,">"&EOMONTH(B6,-1),C$6:C6,"active")>2)*(SUMIF(B$6:B6,">="&EOMONTH(B6,-1)+1,F$6:F6)-SUMIF(B$5:B5,">="&EOMONTH(N(B5),-1)+1,G$5:G5))