I have a table of sales data similar to:

Category Item store1sales store2sales etc.
baking mixes Item a 1 3
baking mixes Item b 3 4
baking supplies Item c 2 5
baking supplies Item d 5 6
etc.

This is a table of 500 rows, with a variable number of categories and items each week. I need a subtotal of sales by like category for each store listed. So what I wind up with would look like:

Category Item store1sales store2sales etc.
baking mixes 4 7
baking supplies 7 11
etc.
Total 11 18

These subtotals need to be on a second sheet, as I want the original (the detail) sheet intact for review. So 2nd sheet would have just categories listed with subtotals.

As always, any help would be greatly appreciated!

Denny Behnfeldt

Hi Denny,

Am Sat, 23 Nov 2013 12:40:43 -0800 (PST) schrieb
:

I have a table of sales data similar to:

Category Item store1sales store2sales etc.
baking mixes Item a 1 3
baking mixes Item b 3 4
baking supplies Item c 2 5
baking supplies Item d 5 6
etc.

This is a table of 500 rows, with a variable number of categories and items each week. I need a subtotal of sales by like category for each store listed. So what I wind up with would look like:

Category Item store1sales store2sales etc.
baking mixes 4 7
baking supplies 7 11
etc.
Total 11 18

the easiest way to do it is a Pivot table
Drag Category to the row area and the storesales to the value area.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Denny,

Am Sat, 23 Nov 2013 21:51:54 +0100 schrieb Claus Busch:

the easiest way to do it is a Pivot table
Drag Category to the row area and the storesales to the value area.

please look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for the workbook "Subtotals"


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,

Thank you for your time and consideration (and now patience!!) I see your solution and thank you for the posting. What you had is what I need to see.
However, when I go to duplicate, the pivot table does odd things. I can't get it to come out right.

I am obviously new to pivot tables, so I must be missing something. I drag the 'category' title to Row Fields, and they look correct, listed in rows.
Where ever I drag the store sales headings, I get either odd horizontal running numbers, or a stacking of totals in one column, instead of across columns.

I am obviously missing something! I did not mention I am using an older Excel version (2003). I assume there is no relatively 'easy' way to accomplish this with code?

Help?

Thanks,
Denny

On Saturday, November 23, 2013 4:16:07 PM UTC-5, Claus Busch wrote:
Hi Denny, Am Sat, 23 Nov 2013 21:51:54 +0100 schrieb Claus Busch: the easiest way to do it is a Pivot table Drag Category to the row area and the storesales to the value area. please look he https://skydrive.live.com/#cid=9378A...121822A3%21326 for the workbook "Subtotals" Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2

Hi Denny,

Am Mon, 25 Nov 2013 06:08:54 -0800 (PST) schrieb
:

I am obviously new to pivot tables, so I must be missing something. I drag the 'category' title to Row Fields, and they look correct, listed in rows.
Where ever I drag the store sales headings, I get either odd horizontal running numbers, or a stacking of totals in one column, instead of across columns.

drag the store sales into the value area. The headings will come
automatically.
You also can download my file and paste your data in. The Pivot table
has a dynamic range name and will suit automatically.You only have to do
a rightclick into the Pivot table and choose "Refresh"

If that will not solve your problem, please upload your file and post
the link or send it to my address.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2