How to Total YTD Income Per Client and YTD Commission Paid Out From Adjacent Columns?

[Sarah Anderson]

Hi everyone. I hope you can help me out here. Pretty Please.

My manager asked me to use Excel to (1) total the YTD income for each
client and to (2) total the YTD commission paid out to each person
earning commission from an ongoing data table that is updated weekly.

Question: What formulas do I use to automatically find the total year
to date (YTD) income comming from clients: Anna, Bobi, and Casy.
Similarly, what formulas do I use to automatically find the total year
to date (YTD) commission paid to Zack and Yani? Since the income and
commission amount is on a adjacent column, will this be difficult?

Please reply in the ng. Thanks.


The table below contains the income and commission data (updated
weekly):

Table:

A B C
D
1 Client Name Income From Client Commission Paid To
Commission Amount
2 Anna 25 Zack
10
3 Bobi 50 Zack
20
4 Bobi 150 Yani
60
5 Anna 75 Yani
30
6 Casy 300 Zack
120



I need these following results to show up under the table above:

A B
9 YTD Income From Client
10 Anna: 100
11 Bobi: 200
12 Casy: 300


15 YTD Commission Paid
16 Zack: 150
17 Yani: 90

 

[Frank Kabel]

Hi
have a look at pivot tables. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[Domenic]

Hi,

Assuming your data starts at A1,

Client Name Income from Client Commission Paid To Commission Amount
Anna 25 Zack 10
Bobi 50 Zack 20
Bobi 150 Yani 60
Anna 75 Yani 30
Casy 300 Zack 120

List names in column A, and in column B put this formula and copy down
as far as you need to:

Anna =SUMPRODUCT(($A$2:$A$6=A10)*($B$2:$B$6))
Bobi
Casy

Again, list names in column A, and in column B put this formula and copy
down as far as you need to:

Zack =SUMPRODUCT(($C$2:$C$6=A15)*($D$2:$D$6))
Yani

Adjust your ranges accordingly.

Hope this helps!

 

[Sarah Anderson]

Hi
have a look at pivot tables. See:
http://www.cpearson.com/excel/pivots.htm

This page (above) shows a macro code that refreshes values in a Pivot
Table. The thing is, I never used an Excel macro before. How do I
insert the code to update my Pivot Table?