I want to Excel

December 16, 2013 2:25 PM

I need some Excel assistance.

I want to sum a bunch of numbers based on the presence of identical data in another column. I know this doesn't make excellent sense the way I've described it, so here's an example:

I have:

Column A  Column B
item_1   1
item_1   5
item_1   4
item_1   1
item_f   3
item_M   1
item_M   1
item_M   1


I want:

Column A  Column B
item_1   11
item_f   3
item_M   3


There's no pattern to either the names in Column A or the number of values I'll be summing. The list is tens of thousands of items long, and there are probably hundreds (maybe thousands) of different Column A items, so eliminating "by hand" manipulation is important. I'm sure there's a way to do this--help me out!

posted by pullayup to Computers & Internet (8 answers total)

I think what you want is a SUMIF

put this into C2
=SUMIF(A:A,A2,B:B)

fill down.
posted by Just this guy, y'know at 2:28 PM on December 16, 2013

I'd probably follow that up with copying columns A and C to a new sheet and doing a remove duplicates on it to get your final list.
posted by Just this guy, y'know at 2:30 PM on December 16, 2013

Best answer: SumIf will work if pullayup only has a few Column-A types.

If there are hundreds, you probably need a Pivot Table. I'm not very experienced with those, unfortunately.
posted by IAmBroom at 2:34 PM on December 16, 2013

Best answer: Remove duplicates/SUMIF will work, but this is what PivotTables are made for.

Go to your data sheet and select the command Insert > PivotTable. Put Column A in the row headings and Column B in the sum values. You should get your desired output.
posted by zixyer at 2:35 PM on December 16, 2013 [3 favorites]

Best answer: I was about to suggest a pivot table if you were feeling up to it.

Assuming Excel 2013 give column A and B a title.
Select both.
Insert Pivot table on new sheet

put column 1 as a row label
put column 2 as a values

tada
posted by Just this guy, y'know at 2:36 PM on December 16, 2013

Best answer: This is exactly what PivotTables are made for--quick tabulations (counts, sums, averages) of one column by the values in another column, especially if you have too many values (or values that are constantly changing) in the second column to set up a ton of SUMIF formulas.

You can probably get a pretty simple tutorial by googling "PivotTables" but if you want to just play around with it, you'd start by selecting both columns--including the top row with the column names (both columns must have names)--and go to Insert > PivotTable. It will make the PivotTable in a new sheet, and you'd pull the name of the first column into the "values" box and the second column into the "rows" box.
posted by iminurmefi at 2:36 PM on December 16, 2013

Best answer: Pivot table, yes. Select the entire data set, insert Pivot table, column 1 is rows, column 2 is values. Remember to choose *sum* for values and not *count*.
posted by jeather at 2:41 PM on December 16, 2013

I live and breathe pivot tables, and share their amazingness with all of my co-workers whenever possible. If and when your data is in concise columns, there is no better tool for counting,summing and sub-totaling.
When you have additional columns of categorization it gets even more useful.
posted by exparrot at 6:56 AM on December 17, 2013