Writing a formula that sums groups of cells based on position in an outline

**sam2323** · 11-09-2007, 12:27 PM

Is there a conditional formula you can use to sum the contents of a set of cells, on the basis that they exist in a group or subgroup, instead of writing a formula for each outline group along the lines of "=C8+C9+C10..." (or using the basic sum command)? Perhaps it can be done with DSUM in some way. Take a look at the image I've linked to and see if you can understand what I'm asking.

http://i58.photobucket.com/albums/g2...ewPicture2.jpg

**Ron Coderre** · 11-09-2007, 12:37 PM

If you used Automatic Subtotals (via the Excel Main Menu: <data><subtotals>), Excel would build the appropriate formulas at each change of Group/SubGroup for you.

Are you looking for something else?

**sam2323** · 11-09-2007, 12:50 PM

ron, that seems to be the right direction, so thanks for that. for now, at least, i can't get the subtotal function to recognize the groups yet. i'm given the choice of calculating subtotals at each change in the categories defined by my column headers (so, each change in "Detail," which would essentially be every line). Any thoughts?

**Ron Coderre** · 11-09-2007, 01:05 PM

Do you have a "Group" column that assigns each detail item to a group? (You'd need one).

Then that would be a Subtotal option (each change in Group)

Actually, with each detail assigned to appropriate groups/subgroups/etc, a Pivot Table might give you even more analysis flexibility.

Your thoughts?

**sam2323** · 11-09-2007, 01:17 PM

my thoughts are i should learn how to use pivot tables then

thanks for your help. i'm on the right track now. i think i can figure out a way to manipulate this to my liking.

**Ron Coderre** · 11-09-2007, 01:42 PM

Might as well learn from the ranking expert on Pivot Tables: Debra Dalgleish.

See the Pivot Tables section of this website:
http://www.contextures.com/tiptech.html

Enjoy.