Spreadsheet Formula Help

Forums › Off topic › Spreadsheet Formula Help

andysh

229 posts

Master Geek
+1 received by user: 16

#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

Referrals:

Tesla: https://ts.la/andrew897313

Sharesies: https://sharesies.com/r/XRGS77

bazzer

3438 posts

Uber Geek
+1 received by user: 267

Trusted

#197953 25-Feb-2009 11:13

Just use a PivotTable?

Balchy

484 posts

Ultimate Geek

Trusted

#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

#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

40 posts

Geek
+1 received by user: 11

#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

229 posts

Master Geek
+1 received by user: 16

#197969 25-Feb-2009 11:53

Thanks all,

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

Andysh

Referrals:

Tesla: https://ts.la/andrew897313

Sharesies: https://sharesies.com/r/XRGS77

bazzer

3438 posts

Uber Geek
+1 received by user: 267

Trusted

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

HP

Shop now for HP laptops and other devices (affiliate link).

nzjwnz

40 posts

Geek
+1 received by user: 11

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