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

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.

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

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

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

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

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

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

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 -