One formula to sum all lookup with different multiplication criteria

[zedoud]

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.


[TABLE="class: grid, width: 448"]
<colgroup><col span="2"><col><col span="3"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Seller[/TD]
[TD]Sold Value[/TD]
[TD] [/TD]
[TD="colspan: 2"]Commission Rule[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]AA[/TD]
[TD="align: center"]1000[/TD]
[TD] [/TD]
[TD]AA[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]BB[/TD]
[TD="align: center"]1500[/TD]
[TD] [/TD]
[TD]BB[/TD]
[TD="align: right"]6%[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]AA[/TD]
[TD="align: center"]2200[/TD]
[TD] [/TD]
[TD]CC[/TD]
[TD="align: right"]4%[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]CC[/TD]
[TD="align: center"]3200[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]BB[/TD]
[TD="align: center"]2000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your help!

P:S: Bonus if the formula includes a lookup for sellors with blank ID !

 

[sheetspread]

Excel 2010
	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
7
8		498
Sheet1

Cell Formulas
Range	Formula
B8	=SUMPRODUCT(SUMIF($D$2:$D$4,$A$2:$A$6,$E$2:$E$4),$B$2:$B$6)

what would a blank return?

 

[zedoud]

Thanks a lot! This works great!

A blank would return the sum of the value of sales of sellers without ID (blanks) * a fixed commission (7%)

If I don't want to modify the database, I could just add to your formula =SUMPRODUCT(SUMIF($D$2:$D$4,$A$2:$A$7,$E$2:$E$4),$B$2:$B$7)+SUMIF($A$2:$A$7,"",$B$2:$B$7)*0,07 (if line 7 was a seller with no ID)

Or could I include this in the SUMPRODUCT?

Thanks!

Thanks!

 

[sheetspread]

If you add an ID of 0 blanks will be summed at the corresponding %:

Excel 2010
	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		3200		0	7%
6	BB	2000
7
8		594
Sheet1

Cell Formulas
Range	Formula
B8	=SUMPRODUCT(SUMIF($D$2:$D$5,$A$2:$A$6,$E$2:$E$5),$B$2:$B$6)

 

[zedoud]

Perfect! Thanks a lot!

 

[zedoud]

Hi again,

I've been trying to add another criteria before multiplying. That is for example as below, only calculate for "Paris" seller. I've been trying with SUMIFS or adding array to the sumproduct formula but I either get nothing or error...

How could I add more criteria to the formula?

If you add an ID of 0 blanks will be summed at the corresponding %:

Excel 2010

	A	B	C	D	E
Seller	Sold Value	Commission Rule
AA	AA
BB	BB
AA	CC
BB

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]city[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]1000[/TD]
[TD="align: right"]¨Paris[/TD]

[TD="align: right"]5%[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]1500[/TD]
[TD="align: right"]NYC[/TD]

[TD="align: right"]6%[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]2200[/TD]
[TD="align: right"]NYC[/TD]

[TD="align: right"]4%[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3200[/TD]
[TD="align: right"]Paris[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7%[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]2000[/TD]
[TD="align: right"]NYC[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]594[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]=SUMPRODUCT(SUMIF($D$2:$D$5,$A$2:$A$6,$E$2:$E$5),$B$2:$B$6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!

 

[sheetspread]

Excel 2010
	A	B	C	D	E
1	Seller	Sold Value	city	Commission Rule
2	AA	1000	Paris	AA	5%
3	BB	1500	NYC	BB	6%
4	AA	2200	NYC	CC	4%
5		3200	Paris	0	7%
6	BB	2000	NYC
7
8	Paris	274
Sheet9

Cell Formulas
Range	Formula
B8	=SUMPRODUCT(SUMIF($D$2:$D$5,$A$2:$A$6,$E$2:$E$5),--($C$2:$C$6=A8),$B$2:$B$6)

 

[zedoud]

Thanks! Didn't know this "--" trick!