Data Filter and AutoSum

[Ket]

Hello,

I have a column of figures in a spreadsheet that has data filters
applied to it.
This column of numbers is Autosummed at the bottom.

If I choose one of the data filters, it will collapse my column of
numbers accordingly. How do I continue to show the autosum total for
this filtered data set?

Hope this is clear and thanks in advance.

Ket
London

 

[broro183]

Hi Ket,

Do you want the filtered data to show the total of just the visible
cells (after filtering) or of all the data that was there before the
filter is applied?

If the former, apply your filter then press the autosum icon, this will
result in the formula appearing as =subtotal(...
(have a look at help [F1] for an explanation of the different types)

If the latter, don't apply the filter, press the autosum icon, this
will result in the formula appearing as =sum(...

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Ket]

Rob,

Thanks for your note. It is the former. I was aware that I could do
the autosum afterwards, I was being lazy ;-) and wondered if I could
have the sum total automatically reflect the filtered data without
the additional mouse clicks?

Any ideas?

 

[Guest]

Try inserting an additional row right on top of your total (if it doesn't
part other data).

HTH
--
Edmund
(Using Excel XP)

Rob,

Thanks for your note. It is the former. I was aware that I could do
the autosum afterwards, I was being lazy ;-) and wondered if I could
have the sum total automatically reflect the filtered data without
the additional mouse clicks?

Any ideas?

Hi Ket,

Do you want the filtered data to show the total of just the visible
cells (after filtering) or of all the data that was there before the
filter is applied?

If the former, apply your filter then press the autosum icon, this will
result in the formula appearing as =subtotal(...
(have a look at help [F1] for an explanation of the different types)

If the latter, don't apply the filter, press the autosum icon, this
will result in the formula appearing as =sum(...

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[broro183]

Hi Ket,

No sorry I don't think it is possible to save the extra mouse click
without using code.
However, if you want to use code, you could put the following in you
personal.xls file & assign it to a toolbar button:
(I have just adapted this from a recorded macro & have not inserted an
error checking so it is not bullet proof)

Sub InsertSubtotal()
Application.ScreenUpdating = False
Dim SubTotalCell As Range
Set SubTotalCell = ActiveCell
Dim SubTotalRange As String
SubTotalCell.End(xlUp).Select
SubTotalRange = Range(Selection, Selection.End(xlUp)).Address
With SubTotalCell
.Value = "=SUBTOTAL(9," & SubTotalRange & ")"
.Style = "Comma"
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
End With
.Columns.AutoFit
End With
SubTotalCell.Select
Application.ScreenUpdating = True
End Sub

To add this to a toolbar button:
[alt + t + c], choose the Commands tab, select the Macros category
click & drag the "custom button" onto one of your toolbars (eg next t
the sum button), right click on the new button, choose "assign macro"
select "insertsubtotal" from the list.
Nb: this is just a simple version which subtotals all continuous dat
in the same column above the active cell.

btw, thanks for your request as I've been meaning to put something lik
this together for myself but have never got around to it until now!

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience..