Using IF function to Sum up Variable Commission Earnings

[la90292]

My monthly commission work sheet is broken down where B is the commission
rate, C is the Invoice Amount and D is the commission earned. B will have
different commission rates, i.e. 10%, 9%, 8% etc. I want to compute the
total amount of all the invoices that earned 10%, 9%, 8% etc. and the total
commission earned for each commission rate. How would I formulate my
summary.


B C E
10% $1,000 $100.00
8% $3500 $280.00
9% $430 $ 38.70
10% $1200 $120.00
9% $1420 $111.60
Summary:
10% $2200 $220.00
9% $1850 $150.30
8% $3500. $280.00

Thanks

 

[JE McGimpsey]

Use a Pivot table. See

http://peltiertech.com/Excel/Pivots/pivotstart.htm

for help.

 

[Biff]

If you don't want to use a pivot table:

Say your unsummarized data is in these ranges:

Rate - B1:B5
Invoice amt - C1:C5
Amt earned - D15

List your commission rates starting in say B7.

In cell C7 enter this formula:

=SUMIF($B$1:$B$5,$B7,C$1:C$5)

Then, drag copy over to D7 then down as needed.

Biff

 

[Guest]

The best way is by using pivot tables, but if you want a formula....
B C D
Inv Comm
1 10% 1000 100
2 8% 1200 96
3 9% 2200 198
4 10% 850 85
5 8% 900 72
6 9% 1200 108
7 10% 1100 110
8 invoice totals for each %
9 10% C9=SUMPRODUCT(--($B$1:$B$7=B9)*$C$1:$C$7)
10 9% C10=SUMPRODUCT(--($B$1:$B$7=B10)*$C$1:$C$7)
11 8% etc

For the commission substitute the $C$1:$C$7 for D17

Harry