Sum Corresponding values

[MEM1]

Hello All,

I have a list of names in one column and several values in a second column. I want to lookup all occurances of each name in column 1 and sum the corresponding values in column 2. Can someone please help me with this?

Example

Scott 1
Jon 2
Scott 3

Lookup "Scott" and return 3+1 = 4

Any help is greatly appreciated.

 

[IRstuff]

Have you tried SUMIF? It seems to be tailor-made for your application.

TTFN

 

[MortenA]

You could also look at Pivot tables if you want an "automaic" sum of all your "labels".

In your example a pivot table would produc a result table looking something like this (you have a add a label in the row above name/values:

Sum of value Name
Jon Scott Grand Total
Total 2 4 6

or the other way around:

Sum of value
Name Total
Jon 2
Scott 4
Grand Total 6


Its very easy to costumise and ideal for making summaries etc.

Best regards

Morten

 

[FH]

Dear MEM1,
There is a simple solution:
1- Use AutoFilter to filter the data on each name
2- Use the SUBTOTAL function to sum the corresponding data in second column (the AutoSum button automatically uses the SUBTOTAL function if used for AutoFiltered tables.

Farzad

 

[yogia]

Going with IRstuff's suggestion of using SUMIF, if your entries are in cells A1:A3, and B1:B3 then use

=SUMIF(A1:A3,"Scott",B1:B3)

to get 4 for data in your post.


Yogi Anand, D.Eng, P.E.
ANAND Enterprises
Computers-Education-Engg-Networking

http://www.anandent.com

yogia@hotmail.com

http://www.oakland.edu/~anand

anand@oakland.edu