I am trying to create a series of sums for a rather long spreadsheet with
mulitple variables. I have several rows on my spread sheet, each categorized
in genres such as "apple", "orange", and "pear." There is also a numerical
value in a separate column for each of my rows that I want to associate with
the categories I mentioned. I want to be able to get the total sum of each
category. It is a very long spreadsheet, and it is not sorted by these
categories because there are other variables involved. Because the
spreadsheet is so long, I don't just want to create a sum formula with each
individual cell selected. Also, I sometimes add and subtract rows, so that
solution would not hold up. Is there a way to associate the numerical value
with the category within a row and then somehow get the sum of each similar
category for the entire spreadsheet. Hope that makes sense. Thanks for your
help.

apple 5
orange 6
pear 10
apple 3
orange 11
pear 4

sum of apple
sum of orange
sum of pear

DataSubTotal will do what you want.

Subtotal on unique items in your list of fruit.


Gord Dibben MS Excel MVP

On Thu, 1 Apr 2010 12:53:01 -0700, Sara
wrote:

I am trying to create a series of sums for a rather long spreadsheet with
mulitple variables. I have several rows on my spread sheet, each categorized
in genres such as "apple", "orange", and "pear." There is also a numerical
value in a separate column for each of my rows that I want to associate with
the categories I mentioned. I want to be able to get the total sum of each
category. It is a very long spreadsheet, and it is not sorted by these
categories because there are other variables involved. Because the
spreadsheet is so long, I don't just want to create a sum formula with each
individual cell selected. Also, I sometimes add and subtract rows, so that
solution would not hold up. Is there a way to associate the numerical value
with the category within a row and then somehow get the sum of each similar
category for the entire spreadsheet. Hope that makes sense. Thanks for your
help.

apple 5
orange 6
pear 10
apple 3
orange 11
pear 4

sum of apple
sum of orange
sum of pear