Excel Question

tsoprano

Not sure if right place to post but have an excel query


I have two columns of information

column 1 are names, column 2 are figures.


I would like to find the total for each name

e.g I have a list

Jane Smith - 1
John Smith - 3
Jane Smith - 6
Mark Smith - 10
Carol Smith - 9
Mark Smith - 90
Carol Smith - 56
Jane Smith - 22
John Smith - 4

I would like a formula to see what the total figures for each name are. e.g John Smith would be 7, Carol would be 65, Jane would be 29.

I realise that this is probably a very simple formula but I have been messing around with countif,dcount for a good while now with the only result being #value!

Any help would be appreciated. Thanks!

PhilCo_2

You can either do a sumif or use a pivot table to get the totals. For a sumif you need to have a list of the unique names and then in the next cell put

=sumif(range where names are,cell with unique name in,range with values in)
so if the names are in A2:A10, values in B2:B10 and unique name in E2 in F2 enter
=SUMIF($A$2:$A$10,E2,$B$2:$B$10) or 
=SUMIF($A$2:$A$10,"Jane Smith",$B$2:$B$10)

For a pivot table, highlight both columns including headings and insert pivot table. Click next if range shown is correct then in the drag the Name column heading into rows and Value column heading into the Value section and it will sum up.

tsoprano

right I'm completely stupid with these kind of things - do I have to create new column headings with the names and use these (column headings) in the formula? or can I just type them in

oK I've got that working but it doesn't seem to be adding them correctly - will check again tomorrow

Thanks again.

danthemoneysavingman

column headings will help for lists.
suggest the pivot table approach as detailed above is easiest

tedster123

ok say column a is names and b is numbers both starting at 2nd row

pivot table is good idea and fairly easy to use if you dont want to do this try this


if you have excel 2007 then you can copy the names to say column c click on data -> remove duplicates this will give you list of unique names

you then in column d type without inverted commas "=sumif($a$2:$a$10,$c$2,$b$2:$b$10) this will sum the unique name of the left

if you have excel 2003 if there are only couple names type in if not:

sort by name and adjacent column offset below by one type
"=if(a3=a2,0,1)" drag this to bottom of sheet then use autofilter - filter down to one and copy to another column to use

littlepanther

Here is a handy short video on how to use SUMIF http://www.jargonfreehelp.com/episode.asp?video_id=excel_sumif

There is also COUNTIF http://www.jargonfreehelp.com/episode.asp?video_id=excel_countif

These functions are so under used, glad to see someone has a use.

LP