salesman commission calculation

RAMU

Active Member
Joined
Dec 11, 2009
Messages
282
Dear Friends,

Calculating Commission:

Main sheet: Cells A1 to H5

SALESMANSALESMAN NOBILL NOBILL DATESOLD AMOUNTPAYMENT RCVDBALANCEDAYS
JOHN20AF-00102-04-201810001000010
JOHN20AF-04214-04-201820002000020
JOHN20AF-05029-04-20183000250050012
JOHN20AF-20120-06-20181000010000014

<tbody>
</tbody>

Target: Cells K2 to J4
TARGET
MONTH5000
QTR15000

<tbody>
</tbody>


Calculation Desired result: Cells N1 to P3

COMMISSION
Apr-18Jun-18QTR-1
50500220

<tbody>
</tbody>


Logics: If he achieves Monthly & qtrly target, then eligible for commission, but the invoices which full payment received within 15 days that will be in calculation. In case of monthly commission it will be 5% of bill value & for qtr that will be 2%.
N3, O3, & P3 are my desired result. Pls help me with formulas month wise & qtr wise.

Pls note monthly & qtr achievement will separately be accepted.
Thanks in advance.
Regards
Ramu
 
Last edited:

miss_ell

Active Member
Joined
Jun 19, 2002
Messages
274
Hello,

Hopefully this should help (columns A to Q, rows 1 to 5). Sorry, I do not know how to correctly post tables with their formulas as Excel Jeanie does not seem to work.


SALESMANSALESMAN NOBILL NOBILL DATESOLD AMOUNTPAYMENT RCVDBALANCEDAYSCOMMISSION?MONTHCOMMISSION
JOHN20AF-0014319210001000010=IF(AND(G2=0,H2<=15),"Y","N")=DATE(YEAR(D2),MONTH(D2),1)TARGET4319143252QTR-1
JOHN20AF-0424320420002000020=IF(AND(G3=0,H3<=15),"Y","N")=DATE(YEAR(D3),MONTH(D3),1)=SUMIFS($E$2:$E$5,$I$2:$I$5,"Y",$J$2:$J$5,O2)=SUMIFS($E$2:$E$5,$I$2:$I$5,"Y",$J$2:$J$5,P2)=SUM(O3:P3)
JOHN20AF-050432193000250050012=IF(AND(G4=0,H4<=15),"Y","N")=DATE(YEAR(D4),MONTH(D4),1)MONTH5000=O3*0.05=P3*0.05=Q3*0.02
JOHN20AF-201432711000010000014=IF(AND(G5=0,H5<=15),"Y","N")=DATE(YEAR(D5),MONTH(D5),1)QTR15000

<tbody>
</tbody>

<tbody>
</tbody>


I have inserted columns I and J (Commision? and Month) as helper columns.
 
Last edited:

miss_ell

Active Member
Joined
Jun 19, 2002
Messages
274
I just realised that the commission should only be calculated if the salesperson reaches their target; therefore the formulas for O4, P4 and Q4 whould be different.

COMMISSION
4319143252QTR-1
=SUMIFS($E$2:$E$5,$I$2:$I$5,"Y",$J$2:$J$5,O2)=SUMIFS($E$2:$E$5,$I$2:$I$5,"Y",$J$2:$J$5,P2)=SUM(O3:P3)
=IF(O3>=$M$4,O3*0.05,0)=IF(P3>=$M$4,P3*0.05,0)=IF(Q3>=$M$5,Q3*0.02,0)

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

RAMU

Active Member
Joined
Dec 11, 2009
Messages
282
Hi,
It works fine but sorry to mention that my another logic is name of salesman like target, month, qtr. In the main sheet I have lots of names. Here I gave instance of John but if I take another name, then how the formula would be ?
 

miss_ell

Active Member
Joined
Jun 19, 2002
Messages
274
Hello,

This should take into consideration more than one salesman:-

SALESMAN



SALESMAN NOBILL NOBILL DATESOLD AMOUNTPAYMENT RCVDBALANCEDAYSCOMMISSION?MONTHTARGETSALESMANSALESCOMMISSION
JOHN20AF-0014319210001000=E2-F210=IF(AND(G2=0,H2<=15),"Y","N")=DATE(YEAR(D2),MONTH(D2),1)4319143252QTR-14319143252QTR-1
JOHN20AF-0424320420002000=E3-F320=IF(AND(G3=0,H3<=15),"Y","N")=DATE(YEAR(D3),MONTH(D3),1)MONTH5000JOHN=SUMIFS($E$2:$E$12,$A$2:$A$12,$O3,$I$2:$I$12,"Y",$J$2:$J$12,$P$2)=SUMIFS($E$2:$E$12,$A$2:$A$12,$O3,$I$2:$I$12,"Y",$J$2:$J$12,$Q$2)=SUM(P3:Q3)=IF(P3>=$M$3,P3*0.05,0)=IF(Q3>=$M$3,Q3*0.05,0)=IF(R3>=$M$4,R3*0.02,0)
JOHN20AF-0504321930002500=E4-F412=IF(AND(G4=0,H4<=15),"Y","N")=DATE(YEAR(D4),MONTH(D4),1)QTR15000PETER=SUMIFS($E$2:$E$12,$A$2:$A$12,$O4,$I$2:$I$12,"Y",$J$2:$J$12,$P$2)=SUMIFS($E$2:$E$12,$A$2:$A$12,$O4,$I$2:$I$12,"Y",$J$2:$J$12,$Q$2)=SUM(P4:Q4)=IF(P4>=$M$3,P4*0.05,0)=IF(Q4>=$M$3,Q4*0.05,0)=IF(R4>=$M$4,R4*0.02,0)
JOHN20AF-201432711000010000=E5-F514=IF(AND(G5=0,H5<=15),"Y","N")=DATE(YEAR(D5),MONTH(D5),1)JAMES=SUMIFS($E$2:$E$12,$A$2:$A$12,$O5,$I$2:$I$12,"Y",$J$2:$J$12,$P$2)=SUMIFS($E$2:$E$12,$A$2:$A$12,$O5,$I$2:$I$12,"Y",$J$2:$J$12,$Q$2)=SUM(P5:Q5)=IF(P5>=$M$3,P5*0.05,0)=IF(Q5>=$M$3,Q5*0.05,0)=IF(R5>=$M$4,R5*0.02,0)
PETER4319320001000=E6-F63=IF(AND(G6=0,H6<=15),"Y","N")=DATE(YEAR(D6),MONTH(D6),1)
PETER431943000030000=E7-F720=IF(AND(G7=0,H7<=15),"Y","N")=DATE(YEAR(D7),MONTH(D7),1)
PETER432101500015000=E8-F810=IF(AND(G8=0,H8<=15),"Y","N")=DATE(YEAR(D8),MONTH(D8),1)
PETER4325225002000=E9-F95=IF(AND(G9=0,H9<=15),"Y","N")=DATE(YEAR(D9),MONTH(D9),1)
PETER4326150004500=E10-F1020=IF(AND(G10=0,H10<=15),"Y","N")=DATE(YEAR(D10),MONTH(D10),1)
JAMES4320660006000=E11-F114=IF(AND(G11=0,H11<=15),"Y","N")=DATE(YEAR(D11),MONTH(D11),1)
JAMES432521800012000=E12-F1210=IF(AND(G12=0,H12<=15),"Y","N")=DATE(YEAR(D12),MONTH(D12),1)

<tbody>
</tbody>
 

RAMU

Active Member
Joined
Dec 11, 2009
Messages
282
Dear Friend,
One more solution that I need, that is from commission amount I want to know the invoices against which the total commission amount covers. It will be back calculated. Pls help.

Hello,

This should take into consideration more than one salesman:-

SALESMAN


SALESMAN NOBILL NOBILL DATESOLD AMOUNTPAYMENT RCVDBALANCEDAYSCOMMISSION?MONTHTARGETSALESMANSALESCOMMISSION
JOHN20AF-0014319210001000=E2-F210=IF(AND(G2=0,H2<=15),"Y","N")=DATE(YEAR(D2),MONTH(D2),1)4319143252QTR-14319143252QTR-1
JOHN20AF-0424320420002000=E3-F320=IF(AND(G3=0,H3<=15),"Y","N")=DATE(YEAR(D3),MONTH(D3),1)MONTH5000JOHN=SUMIFS($E$2:$E$12,$A$2:$A$12,$O3,$I$2:$I$12,"Y",$J$2:$J$12,$P$2)=SUMIFS($E$2:$E$12,$A$2:$A$12,$O3,$I$2:$I$12,"Y",$J$2:$J$12,$Q$2)=SUM(P3:Q3)=IF(P3>=$M$3,P3*0.05,0)=IF(Q3>=$M$3,Q3*0.05,0)=IF(R3>=$M$4,R3*0.02,0)
JOHN20AF-0504321930002500=E4-F412=IF(AND(G4=0,H4<=15),"Y","N")=DATE(YEAR(D4),MONTH(D4),1)QTR15000PETER=SUMIFS($E$2:$E$12,$A$2:$A$12,$O4,$I$2:$I$12,"Y",$J$2:$J$12,$P$2)=SUMIFS($E$2:$E$12,$A$2:$A$12,$O4,$I$2:$I$12,"Y",$J$2:$J$12,$Q$2)=SUM(P4:Q4)=IF(P4>=$M$3,P4*0.05,0)=IF(Q4>=$M$3,Q4*0.05,0)=IF(R4>=$M$4,R4*0.02,0)
JOHN20AF-201432711000010000=E5-F514=IF(AND(G5=0,H5<=15),"Y","N")=DATE(YEAR(D5),MONTH(D5),1)JAMES=SUMIFS($E$2:$E$12,$A$2:$A$12,$O5,$I$2:$I$12,"Y",$J$2:$J$12,$P$2)=SUMIFS($E$2:$E$12,$A$2:$A$12,$O5,$I$2:$I$12,"Y",$J$2:$J$12,$Q$2)=SUM(P5:Q5)=IF(P5>=$M$3,P5*0.05,0)=IF(Q5>=$M$3,Q5*0.05,0)=IF(R5>=$M$4,R5*0.02,0)
PETER4319320001000=E6-F63=IF(AND(G6=0,H6<=15),"Y","N")=DATE(YEAR(D6),MONTH(D6),1)
PETER431943000030000=E7-F720=IF(AND(G7=0,H7<=15),"Y","N")=DATE(YEAR(D7),MONTH(D7),1)
PETER432101500015000=E8-F810=IF(AND(G8=0,H8<=15),"Y","N")=DATE(YEAR(D8),MONTH(D8),1)
PETER4325225002000=E9-F95=IF(AND(G9=0,H9<=15),"Y","N")=DATE(YEAR(D9),MONTH(D9),1)
PETER4326150004500=E10-F1020=IF(AND(G10=0,H10<=15),"Y","N")=DATE(YEAR(D10),MONTH(D10),1)
JAMES4320660006000=E11-F114=IF(AND(G11=0,H11<=15),"Y","N")=DATE(YEAR(D11),MONTH(D11),1)
JAMES432521800012000=E12-F1210=IF(AND(G12=0,H12<=15),"Y","N")=DATE(YEAR(D12),MONTH(D12),1)

<tbody>
</tbody>
 

miss_ell

Active Member
Joined
Jun 19, 2002
Messages
274
The "Y" indicates those invoices which are eligible for commission. You can use these values to get these invoices for each salesperson using SUMIF or SUMIFS. Play around with the formulas and try it out yourself.
 

Forum statistics

Threads
1,082,360
Messages
5,364,920
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top