Politely Asking for Help on a Formula - Thanks!

[rob_chhj]

Hi, my business gives commissions to our drivers (called “captains” as shown in the Excel screenshot). Their commission is 10% of the sale price of a surcharge they sell.

I want to track their daily commissions per captain and then total each daily commission for their weekly total commission per captain.

I already have their daily tracking set up to automatically calculate their daily commissions based on the prices of surcharges and tally of quantity sold for each.

My issue now is making the formula that automatically searches row P5 to T5 for instances of their unique name being written. Each time their name is written in that row indicates one working day with commissions made (total for each daily commission is in row P30 to T30) in their own column.

I would then like the same formula to have N34 to N36 autofill if there are captain’s names found in P5 to T5 (without duplicating their names) and then totaling their daily commissions specific to each captain’s name in the weekly totals at O34 to O36.

I hope this makes sense. Let me know if you need clarification.

I’ve tried for several hours to figure it out with various formulas but I’m not too versed in Excel so I’m hoping someone can help me out. Thanks in advance!

[#2]

@rob_chhj 

=LET(captain,UNIQUE(TRANSPOSE(P5:T5)),
commission,SUMIF($P$5:$T$5,captain,$P$30:$T$30),
result,CHOOSE({1,2},captain,commission),
result)

Maybe with this formula as shown in the attached file. 

[#3 · Accepted solution]

@rob_chhj 

In O34:

=SUMIF($P$5:$T$5, N34, $P$30:$T$30)

Fill down to O36.

[#4]

Awesome! This works! Really appreciate it