subtotaling every third row?

[danielle.travers]

Is it possible to subtotal every third row? I have the following type
of spreadsheet:

Group Style Units
B FENDI MEDIUM CHAIN BAG OH 0
B FENDI MEDIUM CHAIN BAG Sold 1
B FENDI MEDIUM CHAIN BAG Wss 0
B FENDI LARGE CHAIN BAG OH 1
B FENDI LARGE CHAIN BAG Sold 0
B FENDI LARGE CHAIN BAG WSS 0
B FENDI MEDIUM CHAIN BAG OH 1
B FENDI MEDIUM CHAIN BAG Sold 0
B FENDI MEDIUM CHAIN BAG WSS 0

Basically, I need subtotals by Group, as well as a couple of other
columns that I couldn't show in this form (this is a huuuuge
spreadsheet). I need the subtotals to add up the OH, Sold, and WSS--
Is this possible? I'm guessing it is, I'm just starting to realize
what an amazing program Excel is (once you master it)! I thought my
pivot table and V-Lookup skills were impressive, but apparently there
is a whole world of custom lists, VBA etc that I haven't even begun to
touch...Thank you soooooo much for your help, you can't even begin to
understand how much your advice will help!
Best,
Danielle

 

[Roger Govier]

Hi Danielle

Set up your Pivot Table.
Drag Group to the Row area, drag Style to the row area, drag Units to
the data area and ensure it is set to Sum.
Double click on Group and ensure Subtotals is et to Automatic.

 

[Ron Coderre]

Maybe you could try something like this:

If the UNITS are in Col_B and the AMOUNTS are in Col_C

F1: OH
F2: Sold
F3: WSS

G1: =SUMIF(B:B,F1,C:C)
Copy that formula down through G3

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[danielle.travers]

Maybe you could try something like this:

If the UNITS are in Col_B and the AMOUNTS are in Col_C

F1: OH
F2: Sold
F3: WSS

G1: =SUMIF(B:B,F1,C:C)
Copy that formula down through G3

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP







- Show quoted text -

We actually created this report so that we could consolidate the
worksheet. It used to be set up so that oh, sold, and weeks of supply
were across the top by store, but we have acheived significant growth,
and now that we have 30+ stores the date wouldn't fit onto a single
11x17 page if we kept using that format. I believe that's essentially
what the pivot table Roger suggests would set up.

I have tried sumifs or conditional sums, but this is not adequate
because it does not automatically insert rows for subtotals the same
way that the subtotal function does. Since we need a very high number
of subtotals, using conditional sums would work but would be extremely
time consuming. I thought about setting up a macro in which I manually
inserted rows at the necessary intervals and then created conditional
sum formulas, but this will not work because we are constantly adding
or removing styles, so the cell references would be incorrect the next
time I run the macro.

Basically, is there a way to automatically insert a conditional sum at
specific intervals based on changes in column content? For example,
could I subtotal OH, Sld, and WSS for every change in Pillar (there
are 6 changes in Pillar), then at every change in Perm (within each
change in Pillar there is seasonal and permanent), then at every
change in Material (not shown in the snapshot I provided, but there
are probably around 50 changes in material within seasonal or
permanent)? I know that this is probably a very advanced code, but I
figured it's worth a shot-- there are some very smart people in this
group!

Thanks again for all of your help, and I apologize if I'm not being
clear (I wish there was a way to attach spreadsheet examples with
posts).

Best,
Danielle

 

[Roger Govier]

Hi Danielle

If you want send me a copy of the workbook, with an annotated
description of what you are after, I would be pleased to take a look for
you.
To mail direct, remove NOSAPM from my address.