Summing the contents of one column based on the contents of another?

  • Thread starter Thread starter redmist
  • Start date Start date
R

redmist

Hi again everyone,

Ok, after I got several speedy and very helpful responses to my last
query, I figured I could do worse than asking for help here again!
Maybe when I learn some more I can help dish out advice instead of just
asking questions!

Here's my problem:

I have a large spreadsheet with data in columns A and B. Column A
contains lots of strings. Column B contains numbers. eg:

Column A Column B

Piano 4
Saxophone 5
Clarinet 12
Piano 2
Piano 6
Clarinet 1

Ok, what I want to do is get totals for each item. So for the example
above, I'd like to end up with a spreadsheet looking like this:

Column A Column B

Piano 12
Saxophone 5
Clarinet 13

So that duplicates in column A are eliminated and there's just one
entry for each unique string with the total number in column B.

I've been playing around with VBA but not really getting anywhere -
would appreciate some help if anyone's got a little time to spare.

Thanks again,

John.
 
Hiya Red,

you could use the SUMIF worksheet function as follows: -


=SUMIF(A1:A7,"Piano",B1:B7)

This could be typed in any cell (other than one in column A or B). You
will need to change the references to those on your worksheet according
to the following criteria: (available in help)

SUMIF(range,criteria,sum_range)

Range is the range of cells you want evaluated.

Criteria is the criteria in the form of a number, expression, or text
that defines which cells will be added. For example, criteria can be
expressed as 32, "32", ">32", "apples".

Sum_range are the actual cells to sum. The cells in sum_range are
summed only if their corresponding cells in range match the criteria.
If sum_range is omitted, the cells in range are summed.

Hope that helps!
 
John,

You could make a pivot table.

You could use a SUMIF (would require you to keep the current data)
Example Sheet1 A1:B10 has your data.
Summary in Sheet 2 A1:B3

in Sheet 2 B1:
=SUMIF(Sheet1!A$1:A$10,A1,Sheet1!B$1:B$10)

drag down

Dan E
 
Ok, I can see how that works, but I have a couple of thousand rows i
this spreadsheet - is there any way of replacing "Piano" with som
expression that will grab the contents of cell A in the relevan
column? Don't fancy typing out each of the thousand-odd strings
 
Data|Subtotal will work if you sort the data first.

VBA should also accomplish this pretty quickly if you
know how to program.

Mike.
 
Thanks for all the replies everyone!

I ended up using the "subtotal" thingy as it was the simplest way an
did what I needed. I always look for the more complex solution...
 
Back
Top