Spreadsheet Formula Help

Forums › Off topic › Spreadsheet Formula Help

andysh

177 posts

Master Geek

Topic # 30903 25-Feb-2009 11:05

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

bazzer

3295 posts

Uber Geek
+1 received by user: 210

Trusted

Reply # 197953 25-Feb-2009 11:13

Just use a PivotTable?

Balchy

484 posts

Ultimate Geek

Trusted

Reply # 197954 25-Feb-2009 11:16

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?

stevonz

397 posts

Ultimate Geek

Trusted

Reply # 197958 25-Feb-2009 11:31

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

nzjwnz

34 posts

Geek
+1 received by user: 1

Reply # 197960 25-Feb-2009 11:31

Sumif

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

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

andysh

177 posts

Master Geek

Reply # 197969 25-Feb-2009 11:53

Thanks all,

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

Andysh

bazzer

3295 posts

Uber Geek
+1 received by user: 210

Trusted

Reply # 197970 25-Feb-2009 12:04

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...

nzjwnz

34 posts

Geek
+1 received by user: 1

Reply # 197986 25-Feb-2009 13:09

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.