Hi everyone,
I'm trying to solve this problem in an easy way. I have a database (1000s+ lines) with lines of sellers ID's and amount of sales over a time period. Each seller has a different commission percentage. I want to calculate the total amount of commission to be paid in one formula.
So far I have been doing this by having a different tab where I calculate the total commission calculate for each vendors with SUMIF and VLOOKUP:
For example for seller "AA" I want to have (1000+2200)*0.05:
=SUMIF(A:A;"AA";B:B)*VLOOKUP("AA";D:D;2)
And then sum the results I get of each seller vendors
Works fine but I want an easier way to do it in one short formula. Maybe using INDEX and Match? But I'm not to sure how to use it.
<colgroup><col span="2"><col><col span="3"></colgroup><tbody>
</tbody>
Thanks for your help!
P:S: Bonus if the formula includes a lookup for sellors with blank ID !
I'm trying to solve this problem in an easy way. I have a database (1000s+ lines) with lines of sellers ID's and amount of sales over a time period. Each seller has a different commission percentage. I want to calculate the total amount of commission to be paid in one formula.
So far I have been doing this by having a different tab where I calculate the total commission calculate for each vendors with SUMIF and VLOOKUP:
For example for seller "AA" I want to have (1000+2200)*0.05:
=SUMIF(A:A;"AA";B:B)*VLOOKUP("AA";D:D;2)
And then sum the results I get of each seller vendors
Works fine but I want an easier way to do it in one short formula. Maybe using INDEX and Match? But I'm not to sure how to use it.
A | B | C | D | E | |
1 | Seller | Sold Value | Commission Rule | ||
2 | AA | 1000 | AA | 5% | |
3 | BB | 1500 | BB | 6% | |
4 | AA | 2200 | CC | 4% | |
5 | CC | 3200 | |||
6 | BB | 2000 | |
<colgroup><col span="2"><col><col span="3"></colgroup><tbody>
</tbody>
Thanks for your help!
P:S: Bonus if the formula includes a lookup for sellors with blank ID !