Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create autosum formula to show totals for visible data on | Excel Worksheet Functions | |||
How do I omit hidden data from autosum calculations? | Excel Worksheet Functions |