Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Keeping the total of filtered data

Hi there,

I have a list of data in columns and have added a total in each column at
the bottom row. When I filter my data my totals row disappears as well. I
tried defining my data, sum(), subtotal(9; ) but still the totals disappear.

I've read some similar questions and one solution is to place my totals
above the filtered area. This however does not fit the setup of my data so I
want the totals below the filtered data not at the top.

Does anybody have any ideas?
Thanks
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Keeping the total of filtered data

Hi there,

I understand that you are facing an issue with the totals row disappearing when you filter your data in Excel. This can be a common problem, but there are a few ways to solve it.

Solution 1: Use the SUBTOTAL function
  1. Select the cell where you want the total to appear.
  2. Type "
    Code:
    =SUBTOTAL(9,range)
    " where "range" is the range of cells you want to include in the calculation.
  3. Press Enter.

The "9" in the formula tells Excel to use the SUM function, and the function will only include the visible cells in the calculation. This should allow the total to remain visible even when you filter the data.

Solution 2: Use a PivotTable
  1. Select your data range.
  2. Go to the "Insert" tab and click "PivotTable".
  3. In the "Create PivotTable" dialog box, select where you want to place the PivotTable and click "OK".
  4. In the "PivotTable Fields" pane, drag the column you want to summarize to the "Values" area.
  5. Excel will automatically create a total row at the bottom of the PivotTable that will remain visible even when you filter the data.

I hope this helps! Let me know if you have any further questions.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 11,058
Default Keeping the total of filtered data

When you set up the AutoFilter, don't select the entire column or the head
cell. Just select the portion of the column you want filtered and the filter
wil leave the summary row alone.
--
Gary''s Student - gsnu200798


"Costas Limassol" wrote:

Hi there,

I have a list of data in columns and have added a total in each column at
the bottom row. When I filter my data my totals row disappears as well. I
tried defining my data, sum(), subtotal(9; ) but still the totals disappear.

I've read some similar questions and one solution is to place my totals
above the filtered area. This however does not fit the setup of my data so I
want the totals below the filtered data not at the top.

Does anybody have any ideas?
Thanks

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default Keeping the total of filtered data

How about adding an empty row between the data and the total row?

And select the exact range you want filtered--don't include that empty row or
the total row when you apply the filter.

Costas Limassol wrote:

Hi there,

I have a list of data in columns and have added a total in each column at
the bottom row. When I filter my data my totals row disappears as well. I
tried defining my data, sum(), subtotal(9; ) but still the totals disappear.

I've read some similar questions and one solution is to place my totals
above the filtered area. This however does not fit the setup of my data so I
want the totals below the filtered data not at the top.

Does anybody have any ideas?
Thanks


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Keeping the total of filtered data

I've already tried that but for some reason it still does not work. Any
other ideas?

"Gary''s Student" wrote:

When you set up the AutoFilter, don't select the entire column or the head
cell. Just select the portion of the column you want filtered and the filter
wil leave the summary row alone.
--
Gary''s Student - gsnu200798


"Costas Limassol" wrote:

Hi there,

I have a list of data in columns and have added a total in each column at
the bottom row. When I filter my data my totals row disappears as well. I
tried defining my data, sum(), subtotal(9; ) but still the totals disappear.

I've read some similar questions and one solution is to place my totals
above the filtered area. This however does not fit the setup of my data so I
want the totals below the filtered data not at the top.

Does anybody have any ideas?
Thanks



  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Keeping the total of filtered data

I've already tried that but for some reason it still does not work. I have
even named my range just to show exactly the area I indend to filter but no
luck either.

Any other ideas?

"Dave Peterson" wrote:

How about adding an empty row between the data and the total row?

And select the exact range you want filtered--don't include that empty row or
the total row when you apply the filter.

Costas Limassol wrote:

Hi there,

I have a list of data in columns and have added a total in each column at
the bottom row. When I filter my data my totals row disappears as well. I
tried defining my data, sum(), subtotal(9; ) but still the totals disappear.

I've read some similar questions and one solution is to place my totals
above the filtered area. This however does not fit the setup of my data so I
want the totals below the filtered data not at the top.

Does anybody have any ideas?
Thanks


--

Dave Peterson

  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default Keeping the total of filtered data

Just double checking...

You did remove the autofilter arrows and then select the exact range before you
reapplied data|filter|autofilter, right?

Costas Limassol wrote:

I've already tried that but for some reason it still does not work. I have
even named my range just to show exactly the area I indend to filter but no
luck either.

Any other ideas?

"Dave Peterson" wrote:

How about adding an empty row between the data and the total row?

And select the exact range you want filtered--don't include that empty row or
the total row when you apply the filter.

Costas Limassol wrote:

Hi there,

I have a list of data in columns and have added a total in each column at
the bottom row. When I filter my data my totals row disappears as well. I
tried defining my data, sum(), subtotal(9; ) but still the totals disappear.

I've read some similar questions and one solution is to place my totals
above the filtered area. This however does not fit the setup of my data so I
want the totals below the filtered data not at the top.

Does anybody have any ideas?
Thanks


--

Dave Peterson


--

Dave Peterson
  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Keeping the total of filtered data

Yes, I did that. I even tried checking this on different PCs just in case
there was something wrong with my PC.

Unless I am doing something completely stupid then I am beginning to think
that it must be a bug of some sort.


"Dave Peterson" wrote:

Just double checking...

You did remove the autofilter arrows and then select the exact range before you
reapplied data|filter|autofilter, right?

Costas Limassol wrote:

I've already tried that but for some reason it still does not work. I have
even named my range just to show exactly the area I indend to filter but no
luck either.

Any other ideas?

"Dave Peterson" wrote:

How about adding an empty row between the data and the total row?

And select the exact range you want filtered--don't include that empty row or
the total row when you apply the filter.

Costas Limassol wrote:

Hi there,

I have a list of data in columns and have added a total in each column at
the bottom row. When I filter my data my totals row disappears as well. I
tried defining my data, sum(), subtotal(9; ) but still the totals disappear.

I've read some similar questions and one solution is to place my totals
above the filtered area. This however does not fit the setup of my data so I
want the totals below the filtered data not at the top.

Does anybody have any ideas?
Thanks

--

Dave Peterson


--

Dave Peterson

  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default Keeping the total of filtered data

I'd delete the total row.
Then reset the used range
Visit Debra Dalgleish's site:
http://contextures.com/xlfaqApp.html#Unused

Then apply the filter.

Then add the total row after an empty row after the autofilter range.

Maybe it'll work???

Costas Limassol wrote:

Yes, I did that. I even tried checking this on different PCs just in case
there was something wrong with my PC.

Unless I am doing something completely stupid then I am beginning to think
that it must be a bug of some sort.

"Dave Peterson" wrote:

Just double checking...

You did remove the autofilter arrows and then select the exact range before you
reapplied data|filter|autofilter, right?

Costas Limassol wrote:

I've already tried that but for some reason it still does not work. I have
even named my range just to show exactly the area I indend to filter but no
luck either.

Any other ideas?

"Dave Peterson" wrote:

How about adding an empty row between the data and the total row?

And select the exact range you want filtered--don't include that empty row or
the total row when you apply the filter.

Costas Limassol wrote:

Hi there,

I have a list of data in columns and have added a total in each column at
the bottom row. When I filter my data my totals row disappears as well. I
tried defining my data, sum(), subtotal(9; ) but still the totals disappear.

I've read some similar questions and one solution is to place my totals
above the filtered area. This however does not fit the setup of my data so I
want the totals below the filtered data not at the top.

Does anybody have any ideas?
Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
total on filtered data? martyn Excel Worksheet Functions 3 September 5th 06 02:50 PM
KEEPING THE TOTAL IN C3 WHEN ADDING B3 bouncer8 Excel Discussion (Misc queries) 3 August 19th 06 01:21 AM
KEEPING THE TOTAL IN C3 WHEN ADDING B3 bouncer8 Excel Discussion (Misc queries) 1 August 18th 06 02:41 PM
Keeping an acculumative total in a row and only that row. Sherry Excel Worksheet Functions 2 January 7th 06 09:35 PM
How to have total for filtered data?? Derrick Excel Discussion (Misc queries) 2 March 10th 05 03:41 AM


All times are GMT +1. The time now is 06:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"