Summing multiple instances of the same name

richard oldcorn

Board Regular
Joined
Nov 12, 2002
Messages
169
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

richard oldcorn

Board Regular
Joined
Nov 12, 2002
Messages
169
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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

Well-known Member
Joined
Jun 8, 2007
Messages
2,494

ADVERTISEMENT

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

New Member
Joined
Apr 14, 2009
Messages
10
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939

ADVERTISEMENT

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

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
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

Board Regular
Joined
Nov 12, 2002
Messages
169
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top