Data Filter and AutoSum
 

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

Data Filter and AutoSum
 

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
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=541495

Data Filter and AutoSum
 

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?

On Fri, 12 May 2006 05:54:43 -0500, broro183
wrote:


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...

Data Filter and AutoSum
 

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

HTH
--
Edmund
(Using Excel XP)


"Ket" wrote:

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?

On Fri, 12 May 2006 05:54:43 -0500, broro183
wrote:


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...

Data Filter and AutoSum
 

Hi Ket,

No sorry I don't think it is possible to save the extra mouse clicks
without using code.
However, if you want to use code, you could put the following in your
personal.xls file & assign it to a toolbar button:
(I have just adapted this from a recorded macro & have not inserted any
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 to
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 data
in the same column above the active cell.

btw, thanks for your request as I've been meaning to put something like
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...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=541495