Summing multiple instances of the same name

[richard oldcorn]

Using Excel2007 / Vista

Weekly I receive commission payment data. Essentially 2 columns, Col A the client name, Col B the commission amount. I like to sort the data so that I have a total commission amount for each client
Firstly I sort the 2 Cols alphabetically. Where there is only one instance of a client name, the total commission is obvious.
But any client can appear several times (anything up to 6) with corresponding commission amounts for each name occurrence.
One week a client may not appear at all, or they may appear from 1 to 6 times.
I seek a formula which, where there are multiple instances of the same client name. will total all the commission payments for that client. Naturally the formula can be in a new Col.
Thanks and regards,

Richard Oldcorn

 

[Mike Blackman]

Hi,

SUMIF should work for this;

=SUMIF(A:A,"Mike",B:B)

 

[richard oldcorn]

MAny thanks, but this doesn't seem to work even if the only multiple client was Mike...?
Client $ C
a 1 6
b 2 6
b 3 6
c 4 6
d 5 6
mike 6 6
mike 7 6
e 8 6
e 9 6

Regards

Richard, Sydney

 

[schielrn]

Maybe look into using a pivot table.

It allows you to sum or count or average or many other things by summarizing your data so that all like things get summarized together.

Hope that helps and post back with any questions.

 

[Mike Blackman]

Hi,

I'm clearly not understanding the problem, I ran the formula on the data you posted and it retrieved 13 as I had expected it to, what are you actually trying to return?

 

[Trafficcone]

I'd have to agree the a pivot table is the way to go, it will allow you to easily see the total commission and drill down to review the individual commission amounts if required

 

[schielrn]

FWIW, Mikey B's formula should also do what you want?

 

[Mike Blackman]

Certainly a pivot table would be the best solution for what we think the Op wants but if indeed the SUMIF isn't returning what he expects (providing its applied properly) than I'm assuming that there is something we don't yet know

 

[richard oldcorn]

Dear Mikey B

Many thanks for persevereing. Not sure why we are getting different results. I input

Col A Col B Col C
a 1 6
b 2 6
b 3 6
c 4 6
d 5 6
mike 6 6
mike 7 6
e 8 6
e 9 6
e 10 6
f 11 6
g 12 6
g 13 6
g 14 6
g 15 6


having cut and paste =SUMIF(A:A,"Mike",B:B) into Col C and dragged it down the 15 Rows. The formula remained the same from C2 to C16. I have now added a few more Rows so that the result I am looking for is clearer, namelly

Col A Col B Col C
a 1 1
b 2
b 3 5
c 4 4
d 5 5
mike 6
mike 7 13
e 8
e 9
e 10 27
f 11 11
g 12
g 13
g 14
g 15 54


1) I am baffled why you get 13 for Mike, and I only get 6

2) It seems the formula required has to note when the Col A value between consecutive Rows is the same, and total the values in Col B. As soon as the value in any two consecutive (Col A) rows is different it starts the process again, as it were.
Which is clearly tricky.

Thanks a lot for your help and thought input todate,

Regards

Richard

 

[schielrn]

Are you sure that one of your mike's do not have an extra space or something? Thats all I can think of because it should sum both mike's. What do you get if you put this:

=SUMIF(A:A,"*Mike*",B:B)
If you get 13 that means 1 of your mike's has an extra space and it would have to be the one with 7 after it, since it is picking up the 6.

Hope that helps.