A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

EXCLUDE TOTAL FROM FILTER



 
 
Thread Tools Display Modes
  #1  
Old October 13th 09, 10:16 AM posted to microsoft.public.excel.misc
Dave
external usenet poster
 
Posts: 1,389
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??
Ads
  #2  
Old October 13th 09, 10:22 AM posted to microsoft.public.excel.misc
Mike H
external usenet poster
 
Posts: 11,504
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??

  #3  
Old October 13th 09, 10:25 AM posted to microsoft.public.excel.misc
Ms-Exl-Learner
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??

  #4  
Old October 13th 09, 01:43 PM posted to microsoft.public.excel.misc
Dave
external usenet poster
 
Posts: 1,389
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??

  #5  
Old October 13th 09, 04:41 PM posted to microsoft.public.excel.misc
Bob I
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??


  #6  
Old October 13th 09, 04:49 PM posted to microsoft.public.excel.misc
Ms-Exl-Learner
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??

  #7  
Old October 24th 09, 10:32 AM posted to microsoft.public.excel.misc
kamakshi Ganapathy
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
  #8  
Old October 24th 09, 01:07 PM posted to microsoft.public.excel.misc
Pete_UK
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 -


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 02: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 11:24 PM.


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