I need ONLY subtotals copied to a second sheet

[dennis.behnfeldt]

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

 

[Claus Busch]

Hi Denny,

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

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.

 

[Claus Busch]

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 here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Subtotals"


Regards
Claus B.

 

[dennis.behnfeldt]

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 accomplishthis with code?

Help?

Thanks,
Denny

 

[Claus Busch]

Hi Denny,

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

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.