Counting clients to provide a bonus

[trosasco23]

Hi, I'm working on a report where I need to provide a set of bonus points every time there is a count of 5, 8 or 10 "cleared" clients per manager.

This manager would get: (yes, the manager gets the points if the rep makes a sale) (This point system is the LIST TAB also)

5 points per enrolled client
2.5 points per T.O'd client (manager took over call for rep)
-10 points per Lost rep (this means rep didn't make the sale)
an extra 10 points when a manager has 5 Cleared sales
an extra 20 points when a manager has 8 cleared sales
an extra 40 points when a manager has 10 cleared sales

I was able to create a cleared column for all Enrolled and TO'd clients to try to calculate the Cleared Sales but I cant figure out how to do the bonus points unless whoever is doing this report manually counts the sales per manager and then adds the extra 10, 20 or 40 points. Is there a way to have this calculate automatically? Please see attached. TRAINEESREPORT.xlsx

[daffodil11]

I'd probably just use formulas to count them automatically instead of a PivotTable.

[trosasco23]

That's not exactly what I need. I already have those totals in my second pivot. What I need is to figure out how to calculate (for example.. Chad Wilson has more than 5 sales - including T.O's and Enrolled... This would give him a bonus 10 points for having 5 sales.. and then he gets a bonus 20 points for having 8 sales but in this case, he only has 7 sales and 1 loss (LOST REP). Did I create more confusion here? I hope not :/

[daffodil11]

Are you saying that the bonuses are additive?

5 sales - 10 points
8 sales - 30 points
10 sales - 70 points

The formula I used in my example is automatically calculating bonus along with points total.

=SUMPRODUCT(($F$2:$F$10)*($B$2:$B$10=I11))+IFERROR(LOOKUP(SUMPRODUCT(($B$2:$B$10=I11)*($F$2:$F$10>0)),{6,8,10},{10,20,40}),0)

This could be easily modified for additive results of 10, 30, 70.

[trosasco23]

Yes the bonuses are to be added on top of the managers totals according to the CLEARED points. Where would you place this formula?