subtotal problems

[pfenrick]

I'm having trouble with subtotals. I have a large budget, which I hav
filtered depending on categories. After the list is filtered, I wan
to subtotal the amounts of each sub-category. However, when I clic
subtotal, SUM by change in category...it subtotals randomly
disregarding the fact that many of the amounts are in the sam
category. I have the lists sorted so that each sub-category is groupe
together. Why won't the subtotaling work? Is there some hidden thin
I'm not aware of? Please help

 

[Frank Kabel]

Hi
the function SUBTOTAL would sum all visible entries in a filtered
range. It does not subtotal by category.

 

[AlfD]

Hi!

Can I be clear?

You have a database. You are using autofilter. You have applied som
conditions which produce a visible selection. You have then use
Data>Subtotals to do subtotals on this reduced (visible) dataset.

OK?

Now: you say it produces random effects.

Can you describe more precisely what you have asked it to do. e.g
Which column you use for producing the subgroups which will then b
subtotalled. What instructions are you giving (via the tick boxes) a
to which columns(fields) to total? And can you describe the rando
effects?

Al

 

[pfenrick]

Yes, you've got the scenario.
With a filtered list, I sort the items based on a column I've labele
"category." This places all items with identical "category" together.
I highlight the columns "category" and the "amount" column. I the
click Data>Subtotal>at each change in "category">use function "SUM">ad
subtotal to "amount."

Instead of subtotalling at each change in "category" it subtotals afte
each cell (except the next to the last cell, it subtotals 2 of the sam
category) with the Grand Total at the bottom.

Is that more clear? Thanks

 

[AlfD]

Hi!

Curiouser and curiouser...

Could you send me a suitable section of your workbook?
Use (e-mail address removed)

Al

 

[Dave Peterson]

I'm not sure if I understand, but using Data|Filter|autofilter and
data|subtotals can give you (er, me) problems.

When you insert the subtotals, you'll get a formula like:

=subtotal(9,c9:c13)

If you autofilter, then =subtotal() will only include those visible cells. You
can either drop the autofilters and use those outlining symbols to the left of
the row numbers.

Or you could drop the data|subtotals.

I like to put =subtotal(9,a3:a10000)
in row 1 and drag across.

Then when I apply a filter, the subtotals only show for the visible cells.

Another option may be to look at Data|pivottable. Your data doesn't need to be
sorted and it's quick and powerful (once you get the hang of them).

 

[Debra Dalgleish]

Are you sure the entries in the category column are identical? Perhaps
they have different endings, that aren't visible in a narrow column, or
there are space characters at the end of some of the categories.

After you sort the list, select all of the category cells for the first
category.
Retype the category name
Press Ctrl+Enter, to enter the text in all the selected cells.

Then, apply the subtotal, and see if the result for that category improves.

 

[Debra Dalgleish]

Oops! I missed the 'filtered list' part.

If you sort by category first, then filter, then subtotal, you should
get better results.

 

[pfenrick]

Debra,

Thank you. You had the solution. I needed to sort before I filter.
Then the subtotals work. Thanks!

 

[Debra Dalgleish]

You're welcome, and thanks for letting me know that it worked.