# How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

1. ## How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

Hi. This is my first time here so I'm not sure if I attached the excel sheet in the right way but I added a screenshot of it. I'm facing some challenging with calculating the commission and bonus with conditions in excel. I have explained some points below. I would really appreciate if someone can help me to complete the challenges with excel formulas or VB or both.

Book1.PNG

**Notes:**

- Product ID is unique.
- More Products will be added in the future.
- Some products are under a main product (Sub-Product).
- No. of Sub-Products are calculated automatically.
- Category Name is selected from a drop-down list from the right-side
reference area.
- Category Price is automatically pulled from the reference area based
on the selection of the Category Name.

**Challenges:**

- Commission should be calculated based on the Product Level and
Category Name. Product Level depends on how many Products this
Product has on top of it (e.g. Product1 has no Product on top of it
(no main product) but Product5 has 4 products on top of it where
Product4 on top of Product5 and Product3 on top of Product4 up to
Product1) so the Product Level for Product5 is 4. Since Product5 is
Category2 so the Category Commission for Product5 is \$ 200 (From the
reference area on the right side).

- Bonus should be calculated based on the Category Name and must have
atleast 2 Sub-Products with the same Category Name within 30 days as
per the Initial Date (e.g. Product1 has 2 Sub-Products which are
Product2 and Product9 (Both are Category1) but they are more than 30
days difference in the Initial date so Bonus should be 0 (zero). e.g.
Product2 has more than 2 Sub-Products all under the same Category
Name and within 30 days so Bonus should be \$ 20). I have tried the
formula in the attached excel sheet
but couldn't complete it. Please advise with a formula with VB script which can do that.

2. ## Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

Hello learn101 and Welcome to Excel Forum.
It would seem that if product 5 is at level 4 then product 1 should be level zero, however there is no product level zero listed in column O so that I have a feeling I am missing something.
Please manually fill in the correct values for the Commission and Bonus columns (H:I) so that we have something with which to compare the results of our proposed formulas/code.
Let us know if you have any questions.

3. ## Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

Apologies I mistakenly added additional zero to the commissions numbers those are deleted.

Level zero has a different calculation which is not included in this sheet That's why I didn't mention it because the focus in this sheet is on everything after level zero.

I manually filled Commission and Bonus columns (H:I) as requested (attached the new sheet). Also I used a color coding for the calculations under the table and changed few things to be able to explain it. Please let me know if you need further details from my side.

Thank you.

4. ## Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

I am still confused, in fact perhaps a bit more so, about the commissions.
Does the set up of the current file mean that O1:Q5 should no longer be considered?
Based on what I think that I understand, I have added a column (F) to show the product level using: =IF(D2=0,0,INDEX(F\$1:F1,MATCH(D2,A\$1:A1,0))+1)
The formula used in the commission column is: =SUMPRODUCT((F\$18:F\$21=F2)*(A\$18:D\$21))

5. ## Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

I manually filled A18:D21 based on the details on O1:Q5. and these values are only to explain calculations. Seems like I confused you more I'm sorry for that.

The formula you added =IF(D2=0,0,INDEX(F\$1:F1,MATCH(D2,A\$1:A1,0))+1) is perfectly correct. I think I need to add a top level with value "zero" in columns P and Q to match that formula.

The formula you used in commission column is calculating the commission based on values I added in A18:D21. And it's repeating the commission as per the product level which made values in I9:I12 incorrect the correct values should be zeros.

Commissions should be calculated based on product against values in Product ID, Product Level, Category Name, and Category Commissions (1 and 2). I'm using the "No. of Sub-Products" column to validate the commission because if the value is zero then I\m expecting a zero commission.

Let me elaborate on how I manually calculated the \$265 for Product1:

Product 1 is the Main Product for Product2 and 9 (both are Level 1 for Product1).
Product2 and 9 are Category1
So, each will have a commission of \$30 as per the cell "P2"
So, the total commission of Level 1 products for "Product1" is \$60 (which are \$30 + \$30)

Now... Product9 is not a Main Product for any other products so there are no Level 2 products here related to Product 1. However, Product2 is the Main Product for Product3, Product10 and Product11. These 3 products are Level 1 for Product2 and also Level 2 for Product1.
Product3, 10 and 11 are Category2
So, each will have a commission of \$40 as per the cell "Q3"
So, the total commission of Level 2 products for "Product1" is \$120 (which are \$40 + \$40 + \$40)

With the same sequence/steps you will see that total commission of Level 3 and 4 for "Product1" is \$40 and \$45.
And the grand total commission of all Product Levels for Product1 will be \$265 (\$60 + \$120 + \$40 + \$45).

So, commission should not be repeated for the Product Level commission is a grand total once per Product. I usually verify that using the "E" column if value is zero it means the value is zero also for commission.

Hopefully I was able to clarify things further. Thank you.

6. ## Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

Not sure how practical this would be for multiple main products, however it does provide the expected results for commissions for the example.
Columns F:H display the products that are in the first product level for the respective row using: =IFERROR(INDEX(\$A\$3:\$A\$12,AGGREGATE(15,6,(ROW(\$A\$3:\$A\$12)-ROW(\$A\$2))/(\$D\$3:\$D\$12=\$A2),COLUMNS(\$F2:F2))),"")
Columns I:K display the categories for those products using: =IFERROR(INDEX(\$M\$2:\$M\$12,MATCH(F2,\$A\$2:\$A\$12,0)),"")
Columns Q:T display the amounts by product level using: =SUMPRODUCT(SUMIFS(INDEX(\$Z\$3:\$AA\$6,COLUMNS(\$Q\$2:Q\$2),),\$Z\$2:\$AA\$2,INDEX(\$I2:\$K12,COLUMNS(\$Q\$2:Q\$2),)))
Note that any/all of the above columns may be moved and/or hidden for aesthetic purposes.
Column O displays the total amount using: =SUM(Q2:T2)
Let us know if you have any questions.

7. ## Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

Firstly, thank you very much for the formulas I believe I can use them as a workaround. However, in this case I will need to manually add number of columns equal to the number of levels for each of F:H, I:K and Q:T and this either will be too long or I will have to limit the number of levels which will make me thin about additional formula to restrict the calculations of the commissions even if hide those columns.

Is there a way to consolidate/merge the 3 formulas in F:H, I:K and Q:T into on formula to make it reduce the number of manual adding of columns or to even insert it direct to column “O” to avoid any manual steps and mistakes? I tried to merge them but failed to replace the values.

I don't want to distract you focus about the first point but going back to my first post regarding the bonus, I couldn't add a condition to validate the that the number of days must be 30 days or less to apply the bonus. I tried to to us IF with VLOOKUP but I was not able to set the conditions as well as the formula structure in the right way.

8. ## Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

Perhaps someone will be able to consolidate the formulas for calculating commission, however I can't see a way to do that.
As to the bonuses:
1. Columns Z:AD display the dates of level 1 sub product categories appearing more than once in the row using: =IF(K2="","",IF(COUNTIFS(\$K2:\$O2,K2)>1,VLOOKUP(F2,\$A\$2:\$C\$12,3,0),""))
2. The formula for the Bonus column is: =IF(COUNTIFS(Z2:AD2,"<="&C2+30)<2,0,VLOOKUP(Q2,AF\$3:AH\$4,3))
Let us know if you have any questions.

9. ## Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

Hi. I tried to implement all what you advised me but in live production;

I'm using:
506 Product names
8 Product Levels
2 Category Names
2 Category Prices
2 Bonus Amounts

I also added the below columns the same way you did:
30 columns - Sub Products
30 columns - Category
8 columns - Levels
30 columns - Dates For Calculating Bonus

When I applied your formulas it worked fine in all new columns except the Levels columns which affected the final results in the commission column at the end.

Also the formula in the final Bonus didn't work correctly.

I made sure that I follow the exact setup and format you used in your formulas but failed to get the correct results and reviewed everything manytimes.

I also noticed that some extra values are added and repeated in the levels new column .

I'm sorry I can't share the file I'm using now because it's a production one but if I will refer to the last file you share here the incorrect values are in: S and T and U:X .

10. ## Re: How To Calculate Commission and Bonus With Conditions In Excel (Formulas or VB Script)

I'll try to help without seeing a sample of the file, however it may not be easy nor quick.
1. What is the formula for Level 1 and what column is it in? What is the first row? (In the file attached to post #8 the formula is in column U starting in row 2)
2. What is the range for the commissions? (In the file attached to post #8 the range is AI3:AK6 with the column headers in AI2:AK2)
3. What columns are the categories in? (In the file attached to post #8 they are in columns K:O)

There are currently 1 users browsing this thread. (0 members and 1 guests)