Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


177 posts

Master Geek


Topic # 30903 25-Feb-2009 11:05
Send private message

Hey,

Just got a problem. I have got a list of names in one column, and a list of numbers in the column. I want to add the numbers up where the names match.

E.G

Bob  5
Mary 4
Bob  3


Bob's total would be 8, mary's would be 3. Guess you would have to use a match and sum functions, maybe if?

Thanks for the help,

Andysh


Create new topic
3282 posts

Uber Geek
+1 received by user: 208

Trusted

  Reply # 197953 25-Feb-2009 11:13
Send private message

Just use a PivotTable?

484 posts

Ultimate Geek

Trusted

  Reply # 197954 25-Feb-2009 11:16
Send private message

Countif?





For billions of years since the outset of time, every single one of your ancestors survived, every single person on your Mum and Dads side, successfully looked after and passed onto you life.  What are the chances of that like?

397 posts

Ultimate Geek

Trusted

Reply # 197958 25-Feb-2009 11:31
Send private message

Use the subtotal function...

Sort your data by name, highlight the data, then file menu Data> Subtotals

Select At each change in; Name 
Use function: Sum
Add subtotal to: Count




Cheers, Stevo

34 posts

Geek
+1 received by user: 1


  Reply # 197960 25-Feb-2009 11:31
Send private message

Sumif

e.g. assuming names are in column A and number in B

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




177 posts

Master Geek


  Reply # 197969 25-Feb-2009 11:53
Send private message

Thanks all,

I went with the Sumif, seems very simple and works great. Thanks nzjwnz

Andysh

3282 posts

Uber Geek
+1 received by user: 208

Trusted

  Reply # 197970 25-Feb-2009 12:04
Send private message

The  downside is you have to hardcode each name that appears in the list, don't you?  Same with countif (which doesnt solve your problem anyway).


The problem with subtotals is if you add new entries, you have to remove and resort etc.


PivotTable beats all!  Just my opinion, of course.  People don't use PivotTables enough...


34 posts

Geek
+1 received by user: 1


  Reply # 197986 25-Feb-2009 13:09
Send private message

No worries andysh

bazzer:

The  downside is you have to hardcode each name that appears in the list, don't you?  Same with countif (which doesnt solve your problem anyway).


The problem with subtotals is if you add new entries, you have to remove and resort etc.


PivotTable beats all!  Just my opinion, of course.  People don't use PivotTables enough...



or you can just point it to a cell where the name already exists e.g. =SUMIF(A:A,C1,B:B) where C1 is say bob

I agree that a pivot table is often the best solution though.

Create new topic

Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.