Dynamic Arrays Figure 93 Question

[Avra]

I'm working with the table shown in Figure 93 of Dynamic Arrays STP2. I understand how Total Bonus is calculated. Now I want to calculate how much bonus each rep receives. While I can do this by adding a Bonus column and then adding them in my Individual Bonus table (=SUMIFS($F$3:$F$27,$C$3:$C$27,$H13), I want to perform this calculation without needing to create the Bonus column for the VLOOLKUP function (=VLOOKUP(D3,$H$3:$I$7,2). How do I write the formula in I13 to do that?

 

[jasonb75]

Easiest way that I can think of is to change the bonus amounts in I3:I7 to 0,5,7,38 and 50 (running totals, 5+7=12, 5+7+38 = 50 etc), then use a formula like the one below in I13 and fill down.
This is an array formula which normally needs to be confirmed with Ctrl Shift Enter, although I believe that this is no longer a requirement with office 365.

=SUMPRODUCT(($C$3:$C$27=H13)*($D$3:$D$27>=TRANSPOSE($H$3:$H$7))*TRANSPOSE($I$3:$I$7))

 

[Fluff]

How about
=SUM(XLOOKUP(FILTER($D$3:$D$27,$C$3:$C$27=H13),$H$3:$H$7,$I$3:$I$7,0,-1))

 

[Avra]

jasonb75, this does work. I didn't think about TRANSPOSE. I think I like Fluff's solution better because then I do not have to manipulate the original data. jason75b and Fluff, thank you so much for these solutions!

 

[Fluff]

Glad we could help & thanks for the feedback.