multiple condition & multiple solution in excel

Question

Please help if possible.

I want to create a dashboard in excel, where I want to calculate the total sales of the agents in my shift. But the problem is that I want to calculate multiple agent's sale score in a single case as well.

Conditions:

• If in a single sale, there are 3 agents, the sale would be divided among the three with the 3rd person getting 50% of the sale amount and the 1st and the 2nd person both getting 25%.

• If in a single sale, there are 2 agents, the sale would be divided among them equally.

• If a single agent cracks the sale, the amount would belong to him alone.

Now how would I put a formula where these things are calculated automatically?

e.g.:

Agent 1     Agent 2     Agent 3    Sale Amount 
Adam                                $100 
Jack         Adam                   $100 
Nick         Vince      Adam        $100 
Vince                               $100 

According to this, the amount would be as such:

Adam: - $100 + $50 + $50 = $200
Jack: - $50 = $50
Nick: - $25 = $25
Vince: - $25 + $100 = $125

This is the kind of result I want but want to get it done automatically as soon as the sale is entered. Anyone know how to achieve it through formula?

Thanks in Advance!

Answer 1

Hope this help you out. I tested it and it appears to work.

Answer 2

You can try following formula:

=SUMPRODUCT(CHOOSE(MMULT(--($A$2:$C$5<>""),{1;1;1}),{1,0,0},{0.5,0.5,0},{0.25,0.25,0.5})*($A$2:$C$5=G1)*$D$2:$D$5)

if necessary change the ranges automatically in the formula, convert main range to table: