Spreadsheet: 748745d1632321855-how-to-calculate-commission-and-bonus-with-conditions-in-excel-formulas-or-vb-script-index-previous-values-in-the-same-column-learn101-
Sheet: Sheet1

Product ID | Product Name | Initial Date | Main Product (Product ID) | No. of Sub-Products | Sub Products |  |  |  |  | Category |  |  |  |  | Product Level | Category Name | Category Price | Commission | Bonus | Level 1 | Level 2 | Level 3 | Level 4 |  | Dates For Calculating Bonus |  |  |  |  |  |  |  |  |  | Commissions
394 | Products1 | 2021-04-30 00:00:00 | 0 |  |  |  |  |  |  |  |  |  |  |  |  | Category2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Category Name | Category Price | Bonuse Amount | Product Level | Category1 | Category2
395 | Products2 | 2021-04-30 00:00:00 | 394 |  |  |  |  |  |  |  |  |  |  |  |  | Category1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Category1 | 100 | 10 | 1 | 30 | 50
396 | Products3 | 2021-09-01 00:00:00 | 395 |  |  |  |  |  |  |  |  |  |  |  |  | Category2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Category2 | 450 | 20 | 2 | 20 | 40
397 | Products4 | 2021-09-01 00:00:00 | 396 |  |  |  |  |  |  |  |  |  |  |  |  | Category2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3 | 10 | 30
398 | Products5 | 2021-09-02 00:00:00 | 397 |  |  |  |  |  |  |  |  |  |  |  |  | Category2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 4 | 5 | 20
399 | Products6 | 2021-09-02 00:00:00 | 397 |  |  |  |  |  |  |  |  |  |  |  |  | Category1
400 | Products7 | 2021-09-03 00:00:00 | 397 |  |  |  |  |  |  |  |  |  |  |  |  | Category2
401 | Products8 | 2021-11-03 00:00:00 | 396 |  |  |  |  |  |  |  |  |  |  |  |  | Category1
402 | Products9 | 2021-11-04 00:00:00 | 394 |  |  |  |  |  |  |  |  |  |  |  |  | Category1
403 | Products10 | 2021-09-01 00:00:00 | 395 |  |  |  |  |  |  |  |  |  |  |  |  | Category2
404 | Products11 | 2021-09-01 00:00:00 | 395 |  |  |  |  |  |  |  |  |  |  |  |  | Category2
[NAME] in E2: =IF(A2>0,COUNTIF(D:D,A2),0)
Formula in G2: =IF(A2>0,VLOOKUP(F2,K:L,2,FALSE),0)
[NAME] in I2: =IF(E2>=2,VLOOKUP(F2,K:M,3,FALSE),0) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 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 inthe attached excel sheet but couldn't complete it. Please advise with a furmula with VB script which can do that.