Sum by different sorts
 

I have a simple spreadsheet. While working on it I constantly need to
sort by different columns and I have several macros to do just that.

The problem:
For the various sorts I also need to sum the columns by their
respective categories. Hear is a simple representation of what I am
trying to do.

Item Type Qty
----------------------------
Apple Fruit 4
Pear Fruit 10
Apple Fruit 4
Potato Veg 6
Fish Meat 22

Ex: When I sort by "Item" I want the sum for each one of the
following: Apples, Pears, Potatoes, and Fish.

When I sort by "Type" I want the sum for each one of the following:
Fruit Veg Meat.

Currently after each sort, I manually insert a row between each
category and use the Sum function to get what I want. But when I sort
by something else, and then come back to a previous sort the sum rows
I inserted are no longer in the correct place. (They end up at the
bottom.)

There has got to be a better way.
Thanks

Sum by different sorts
 

And the better way is Pivot Table.
See one or more of these
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2...le-parameters/
then come back with any questions
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"livetohike" wrote in message
oups.com...
I have a simple spreadsheet. While working on it I constantly need to
sort by different columns and I have several macros to do just that.

The problem:
For the various sorts I also need to sum the columns by their
respective categories. Hear is a simple representation of what I am
trying to do.

Item Type Qty
----------------------------
Apple Fruit 4
Pear Fruit 10
Apple Fruit 4
Potato Veg 6
Fish Meat 22

Ex: When I sort by "Item" I want the sum for each one of the
following: Apples, Pears, Potatoes, and Fish.

When I sort by "Type" I want the sum for each one of the following:
Fruit Veg Meat.

Currently after each sort, I manually insert a row between each
category and use the Sum function to get what I want. But when I sort
by something else, and then come back to a previous sort the sum rows
I inserted are no longer in the correct place. (They end up at the
bottom.)

There has got to be a better way.
Thanks

Sum by different sorts
 

On Nov 1, 1:05 pm, "Bernard Liengme"
wrote:
And the better way is Pivot Table.
See one or more of thesehttp://www.cpearson.com/excel/pivots.htmhttp://peltiertech.com/Excel/Pivots/pivotstart.htmhttp://www.contextures.com/xlPivot02.htmlhttp://www.ozgrid.com/Excel/excel-pivot-tables.htmhttp://www.techonthenet.com/excel/pivottbls/index.htmhttp://www.dicks-blog.com/archives/2005/06/23/download-pivottable-par...
then come back with any questions
best wishes
--
Bernard V Liengme
Microsoft Excel MVPwww.stfx.ca/people/bliengme
remove caps from email

"livetohike" wrote in message

oups.com...

I have a simple spreadsheet. While working on it I constantly need to
sort by different columns and I have several macros to do just that.

The problem:
For the various sorts I also need to sum the columns by their
respective categories. Hear is a simple representation of what I am
trying to do.

Item Type Qty
----------------------------
Apple Fruit 4
Pear Fruit 10
Apple Fruit 4
Potato Veg 6
Fish Meat 22

Ex: When I sort by "Item" I want the sum for each one of the
following: Apples, Pears, Potatoes, and Fish.

When I sort by "Type" I want the sum for each one of the following:
Fruit Veg Meat.

Currently after each sort, I manually insert a row between each
category and use the Sum function to get what I want. But when I sort
by something else, and then come back to a previous sort the sum rows
I inserted are no longer in the correct place. (They end up at the
bottom.)

There has got to be a better way.
Thanks


Great! This pretty much did it. One question:
My data has a date column (m/d/y). I need to see the totals by year
(2005, 2006, etc.), not by individual day (e.g. 4/6/05) which is what
I am getting.

A single pivot for all years, or separate ones for each year would be
OK. I see that I could go back and add a column that calculates the
year from the full date and use that, or use different worksheets for
each year, but I suspect there is a better way.

Thanks a heap.