Excel Subtotals Horizontally as well as vertically

[Welshbird]

I've been sent a spreadsheet that I've been asked to replicate with slightly different data.

The one I have been sent has two sets of subtotals - one by rows and one by columns.

I've never seen subtotals by columns before! The spreadsheet has data for each month across the column, and then products down the side
eg
Prod Jan06 Feb06 Mar06
Beans 11 14 12
Peas 12 17 82

and this has subtotals for groups of products, but also subtotals (going across) for YTD, MAT etc.

Anyone have idea how I can do this? I can't find anything in the help files, and playing with the menu options haven't helped yet either, so I'd really apppreciate any ideas.

Oh, I'm using Excel 2003 SP2 on an XP Pro box if that makes a huge diifference.

Thanks guys.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

Ahhh - It could be a group in same cases, but sometimes it seems to be a subtotal - any ideas?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[CBasicAsslember]

Look at the SUM function.

 

[Welshbird]

I have, but this has the little [+] and [-] signs along the top as well as down the side.

I suspect it is using grouping and outlining, but I've never used that before (didn't know it existed!) so I'm trying to find a tutorial about it.

If anyone can point me at something they've used that'd be great.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[arthurbr]

You will find the Subtotal under Data
If you want to eliminate the subtotals, click Subtotal - " Remove all" - Ok ( after making a copy).
Excel's Help will point you in the right direction

 

[BlueHorizon]

Hi,
It sounds like you could have an AutoOutline applied (Data/Group and Outline/AutoOutline), in which Excel will add those + and - at the top anywhere where there's a formula. In this way, you can expland/collapse different levels.

If you have formulas horizontally, these feature will work for you.

HTH,

Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[SkipVought]

Hi Fee,

If your source data were NORMALIZED, you could accomplish what you want, subtotal by Quarter, Year, whatever across, can be accomplished quite easily using the PivotTable Wizard. It may also take another column to identify the groups that you want, but, again, it's pretty simple stuff.

Given a properly designed Source Data Table, I, or any savvy Excel user, could do it (helper group column and all) in less than 2 minutes.

Here's an example I wipped up. It's VERTICAL simply because to DISPLAYS her better, but it would take 5 SECONDS to chage the orientation to HORIZONTAL...
[tt]
Sum of amt
Years Qtr date Total
2007 20070 Jan 1580
Feb 1356
Mar 1407
20070 Total 4343
20071 Apr 1666
May 1471
Jun 1464
20071 Total 4601
20072 Jul 1540
Aug 1723
Sep 1558
20072 Total 4821
20073 Oct 1488
Nov 1423
Dec 1777
20073 Total 4688
2007 Total 18453
2008 20080 Jan 1609
Feb 366
20080 Total 1975
2008 Total 1975
Grand Total 20428
[tt]


Skip,

_[red][/red]