Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,388
Default EXCLUDE TOTAL FROM FILTER

Hi

I am trying to create a row which will only sum whatever is in a filtered
columns.

Can this be done??
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: EXCLUDE TOTAL FROM FILTER

Yes, it is possible to create a row that will only sum whatever is in a filtered column. Here are the steps to do it:
  1. Select the cell where you want to display the sum.
  2. Type the formula "=SUBTOTAL(9,range)" in the cell, where "range" is the range of cells you want to sum.
  3. Press Enter.

The "9" in the formula tells Excel to use the SUM function, and the "range" argument specifies the range of cells to sum. The SUBTOTAL function will only sum the visible cells in the filtered column, excluding any hidden cells.

Note: Make sure that the range you specify in the formula includes only the column you want to sum, and not any other columns that may be adjacent to it.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default EXCLUDE TOTAL FROM FILTER

Hi,

Do you mean visible cells in a filtered column. If so use SUBTOTAL

=SUBTOTAL(109,B2:B8)

109 makes to sum only visible rows.

Mike

"Dave" wrote:

Hi

I am trying to create a row which will only sum whatever is in a filtered
columns.

Can this be done??

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 506
Default EXCLUDE TOTAL FROM FILTER

Try this€¦

Use subtotal.

For example:-
=SUBTOTAL(9,B2:B4)

Change the Range B2:B4 to your desired cell range.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Dave" wrote:

Hi

I am trying to create a row which will only sum whatever is in a filtered
columns.

Can this be done??

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,388
Default EXCLUDE TOTAL FROM FILTER

Hi

The only problem is when I filter the column, the row with the subtotal in
dissparears. How do i keep the row in the filter?

Thanks

"Mike H" wrote:

Hi,

Do you mean visible cells in a filtered column. If so use SUBTOTAL

=SUBTOTAL(109,B2:B8)

109 makes to sum only visible rows.

Mike

"Dave" wrote:

Hi

I am trying to create a row which will only sum whatever is in a filtered
columns.

Can this be done??



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,819
Default EXCLUDE TOTAL FROM FILTER

Perhaps make your data into a table and place the "subtotal" above the
table.

Dave wrote:

Hi

The only problem is when I filter the column, the row with the subtotal in
dissparears. How do i keep the row in the filter?

Thanks

"Mike H" wrote:


Hi,

Do you mean visible cells in a filtered column. If so use SUBTOTAL

=SUBTOTAL(109,B2:B8)

109 makes to sum only visible rows.

Mike

"Dave" wrote:


Hi

I am trying to create a row which will only sum whatever is in a filtered
columns.

Can this be done??


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 506
Default EXCLUDE TOTAL FROM FILTER

In your below query you have asked that you want to do sum for the data when
it is in Auto Filter, So first apply Autofilter and put the subtotal formula
to get the visible cells total.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Dave" wrote:

Hi

The only problem is when I filter the column, the row with the subtotal in
dissparears. How do i keep the row in the filter?

Thanks

"Mike H" wrote:

Hi,

Do you mean visible cells in a filtered column. If so use SUBTOTAL

=SUBTOTAL(109,B2:B8)

109 makes to sum only visible rows.

Mike

"Dave" wrote:

Hi

I am trying to create a row which will only sum whatever is in a filtered
columns.

Can this be done??

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default sum of filtered rows

use autofilter command and filter the rows. then go to the last row and click the cell where u need the total and pres alt+=. this will trigger subtotal command u will be able to view only sum of filtered rows



Dave wrote:

EXCLUDE TOTAL FROM FILTER
13-Oct-09

Hi

I am trying to create a row which will only sum whatever is in a filtered
columns.

Can this be done??

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Secure Session State Transfer: ASP to ASP.NET
http://www.eggheadcafe.com/tutorials...tate-tran.aspx
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default EXCLUDE TOTAL FROM FILTER

If you put the subtotal formula in the first available row after the
filtered data, then Excel will adjust the filter range to include that
row next time you filter.

You will need to leave at least one blank row between the bottom of
your data and your subtotal formula.

Alternatively, you can insert a new blank row at the top of your sheet
and put the subtotal formula on that row - it saves you having to
scroll down to the bottom everytime you change the filter setting.

Hope this helps.

Pete

On Oct 13, 1:43*pm, Dave wrote:
Hi

The only problem is when I filter the column, the row with the subtotal in
dissparears. How do i keep the row in the filter?

Thanks



"Mike H" wrote:
Hi,


Do you mean visible cells in a filtered column. If so use SUBTOTAL


=SUBTOTAL(109,B2:B8)


109 makes to sum only visible rows.


Mike


"Dave" wrote:


Hi


I am trying to create a row which will only sum whatever is in a filtered
columns.


Can this be done??- Hide quoted text -


- Show quoted text -


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
Exclude data from Pivot Table based on filter? Eric Excel Discussion (Misc queries) 2 October 14th 08 11:55 PM
exclude amount from sumif total based on how many months Vercingetorix.XIII[_2_] Excel Discussion (Misc queries) 1 August 29th 08 09:36 PM
How do I exclude a detail measure value and only view Grand Total boston_sql92 Excel Discussion (Misc queries) 1 March 13th 08 03:29 AM
How Do I Do an Exclude Filter based on a Named Range? Dawg House Inc Excel Worksheet Functions 8 April 11th 06 02:49 AM
HOW DO I TOTAL A FILTERED LIST TO EXCLUDE THOSE EXCLUDED BY FILTE. paul abc Excel Worksheet Functions 2 August 11th 05 07:37 PM


All times are GMT +1. The time now is 05:52 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"