salesman commission calculation

RAMU

Active Member
Dear Friends,

Calculating Commission:

Main sheet: Cells A1 to H5

 SALESMAN SALESMAN NO BILL NO BILL DATE SOLD AMOUNT PAYMENT RCVD BALANCE DAYS JOHN 20 AF-001 02-04-2018 1000 1000 0 10 JOHN 20 AF-042 14-04-2018 2000 2000 0 20 JOHN 20 AF-050 29-04-2018 3000 2500 500 12 JOHN 20 AF-201 20-06-2018 10000 10000 0 14

<tbody>
</tbody>

Target: Cells K2 to J4
 TARGET MONTH 5000 QTR 15000

<tbody>
</tbody>

Calculation Desired result: Cells N1 to P3

 COMMISSION Apr-18 Jun-18 QTR-1 50 500 220

<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.
Regards
Ramu

Last edited:

miss_ell

Active Member
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.

 SALESMAN SALESMAN NO BILL NO BILL DATE SOLD AMOUNT PAYMENT RCVD BALANCE DAYS COMMISSION? MONTH COMMISSION JOHN 20 AF-001 43192 1000 1000 0 10 =IF(AND(G2=0,H2<=15),"Y","N") =DATE(YEAR(D2),MONTH(D2),1) TARGET 43191 43252 QTR-1 JOHN 20 AF-042 43204 2000 2000 0 20 =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) JOHN 20 AF-050 43219 3000 2500 500 12 =IF(AND(G4=0,H4<=15),"Y","N") =DATE(YEAR(D4),MONTH(D4),1) MONTH 5000 =O3*0.05 =P3*0.05 =Q3*0.02 JOHN 20 AF-201 43271 10000 10000 0 14 =IF(AND(G5=0,H5<=15),"Y","N") =DATE(YEAR(D5),MONTH(D5),1) QTR 15000

<tbody>
</tbody>

<tbody>
</tbody>

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

Last edited:

miss_ell

Active Member
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 43191 43252 QTR-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
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
Hello,

This should take into consideration more than one salesman:-

 SALESMAN SALESMAN NO BILL NO BILL DATE SOLD AMOUNT PAYMENT RCVD BALANCE DAYS COMMISSION? MONTH TARGET SALESMAN SALES COMMISSION JOHN 20 AF-001 43192 1000 1000 =E2-F2 10 =IF(AND(G2=0,H2<=15),"Y","N") =DATE(YEAR(D2),MONTH(D2),1) 43191 43252 QTR-1 43191 43252 QTR-1 JOHN 20 AF-042 43204 2000 2000 =E3-F3 20 =IF(AND(G3=0,H3<=15),"Y","N") =DATE(YEAR(D3),MONTH(D3),1) MONTH 5000 JOHN =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) JOHN 20 AF-050 43219 3000 2500 =E4-F4 12 =IF(AND(G4=0,H4<=15),"Y","N") =DATE(YEAR(D4),MONTH(D4),1) QTR 15000 PETER =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) JOHN 20 AF-201 43271 10000 10000 =E5-F5 14 =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) PETER 43193 2000 1000 =E6-F6 3 =IF(AND(G6=0,H6<=15),"Y","N") =DATE(YEAR(D6),MONTH(D6),1) PETER 43194 30000 30000 =E7-F7 20 =IF(AND(G7=0,H7<=15),"Y","N") =DATE(YEAR(D7),MONTH(D7),1) PETER 43210 15000 15000 =E8-F8 10 =IF(AND(G8=0,H8<=15),"Y","N") =DATE(YEAR(D8),MONTH(D8),1) PETER 43252 2500 2000 =E9-F9 5 =IF(AND(G9=0,H9<=15),"Y","N") =DATE(YEAR(D9),MONTH(D9),1) PETER 43261 5000 4500 =E10-F10 20 =IF(AND(G10=0,H10<=15),"Y","N") =DATE(YEAR(D10),MONTH(D10),1) JAMES 43206 6000 6000 =E11-F11 4 =IF(AND(G11=0,H11<=15),"Y","N") =DATE(YEAR(D11),MONTH(D11),1) JAMES 43252 18000 12000 =E12-F12 10 =IF(AND(G12=0,H12<=15),"Y","N") =DATE(YEAR(D12),MONTH(D12),1)

<tbody>
</tbody>

RAMU

Active Member
Oh!!!! GR8....Thanks a lot

RAMU

Active Member
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 NO BILL NO BILL DATE SOLD AMOUNT PAYMENT RCVD BALANCE DAYS COMMISSION? MONTH TARGET SALESMAN SALES COMMISSION JOHN 20 AF-001 43192 1000 1000 =E2-F2 10 =IF(AND(G2=0,H2<=15),"Y","N") =DATE(YEAR(D2),MONTH(D2),1) 43191 43252 QTR-1 43191 43252 QTR-1 JOHN 20 AF-042 43204 2000 2000 =E3-F3 20 =IF(AND(G3=0,H3<=15),"Y","N") =DATE(YEAR(D3),MONTH(D3),1) MONTH 5000 JOHN =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) JOHN 20 AF-050 43219 3000 2500 =E4-F4 12 =IF(AND(G4=0,H4<=15),"Y","N") =DATE(YEAR(D4),MONTH(D4),1) QTR 15000 PETER =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) JOHN 20 AF-201 43271 10000 10000 =E5-F5 14 =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) PETER 43193 2000 1000 =E6-F6 3 =IF(AND(G6=0,H6<=15),"Y","N") =DATE(YEAR(D6),MONTH(D6),1) PETER 43194 30000 30000 =E7-F7 20 =IF(AND(G7=0,H7<=15),"Y","N") =DATE(YEAR(D7),MONTH(D7),1) PETER 43210 15000 15000 =E8-F8 10 =IF(AND(G8=0,H8<=15),"Y","N") =DATE(YEAR(D8),MONTH(D8),1) PETER 43252 2500 2000 =E9-F9 5 =IF(AND(G9=0,H9<=15),"Y","N") =DATE(YEAR(D9),MONTH(D9),1) PETER 43261 5000 4500 =E10-F10 20 =IF(AND(G10=0,H10<=15),"Y","N") =DATE(YEAR(D10),MONTH(D10),1) JAMES 43206 6000 6000 =E11-F11 4 =IF(AND(G11=0,H11<=15),"Y","N") =DATE(YEAR(D11),MONTH(D11),1) JAMES 43252 18000 12000 =E12-F12 10 =IF(AND(G12=0,H12<=15),"Y","N") =DATE(YEAR(D12),MONTH(D12),1)

<tbody>
</tbody>

miss_ell

Active Member
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.

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

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...