Sorting Subtotals

[Katy]

I have a spreadsheet that I am subtotaling. Most of the subtotals are zero,
but I am only concerned with the subtotals that are not. I need to keep the
detail. How can I segregate what has a zero total from another subtotal
without manually looking through the spreadsheet and copying and pasting? I
have already tried sorting by the total column, but it won't let me. FYI, I
am using Excel 2007.

Thanks!!

 

[Shane Devenshire]

Hi,

What do you mean by "segregate"?

 

[Shane Devenshire]

Hi,

If hidding the 0 subtotals is sufficient then turn on AutoFilter and in the
column being subtotaled pick Custom from the autofilter drop-down and choose
does not equal from the first box and enter 0 in the second box. This will
hide all rows with 0's.

Likewise you can autofilter for 0's only.

 

[Katy]

Seperate. I need to put the ones with a total other than zero (including the
detail) on another tab so I can analyze the data and find out why there is
not two offsetting numbers.

 

[Eduardo]

Hi,
Once you have your subtotals in place, highlight the whole range go to Home,
in Stlyes choose format as a table, then take the column where you have the
subtotals and uncheck what is 0 or "Blanks"

If this helped you please say yes, thank you

 

[Katy]

It hides the subtotals with zeros, but the detail that makes up the zero
subtotal is left there which is what I am trying to get rid of.

 

[Katy]

Eduardo,

It doesn't give me the option to uncheck zero or blanks, besides I think
that would just eliminate the zero subtotal and not what makes up the zero
subtotal.

 

[Eduardo]

Hi Katy,
Let's say you have 2 columns A and B in B you have the values, then you
highlight the range and apply the excel subtotals ( Data, Outline, Subtotal).
This create the subtotals. then you highlight again all the range including
the subtotals and format it as a table. If you go to the column B filters and
uncheck the "0" values and the "Blank" values it will eliminate both the 0
totals and the rows that made the 0

 

[Dave Peterson]

I'd add another column and use a formula on each row that essentially did the
subtotal for that group. Yep, you'd see the subtotal on each row of that group.

You may be able to use a function like =sumif() or =sumproduct() or =sumifs() to
do the subtotal.

 

[Dave Peterson]

ps. then use data|filter|autofilter (or the xl2007 equivalent) to show the rows
that have non-zero subtotals.

 

[Katy]

Thanks for your help guys! What I ended up having to do is create a pivot
table, copy and paste the rows with values, and then do a vlookup back to the
original data (without subtotals) and sorting by and deleting out the NA's.

 

[Shane Devenshire]

Hi,

Suppose your grouping is based on column A and the subtotals are in column
B, in a blank column, say C enter the following formula

=SUMIF($A$2:$A$23,A2,$B$2:$B$23)

This assumes titles on the first row. Use the filtering method I described
in my earlier post, but now on column C.

If this helps, please click the Yes button,

cheers,
Shane Devenshire