sum of visable cells in filtered range
 

I have a sheet to track hours of employees for a project. I used the auto-sum
to total the hours range of cells. I would like to format a separate
calculation to total only those cells displayed in a filtered range. As I
change the filter, the total should again include on the new visable cells in
the range. Help!
--
RHino56

sum of visable cells in filtered range
 

This is actually a very good question. What's the good of being able to
filter is we can't process the filtered data. See SUBTOTAL in Excel Help.
--
Gary''s Student - gsnu200789


"RHino56" wrote:

I have a sheet to track hours of employees for a project. I used the auto-sum
to total the hours range of cells. I would like to format a separate
calculation to total only those cells displayed in a filtered range. As I
change the filter, the total should again include on the new visable cells in
the range. Help!
--
RHino56

sum of visable cells in filtered range
 

Thanks, Gary's Student. The final value is what I was looking for but, is
there any way to have this subtotal in a dedicated cell location instead of
adding it to the bottom of the list? Now the subtotal is also added into the
auto-sum value for the column. I am using MS Ofc 2003 SP3.
RHino56


"Gary''s Student" wrote:

This is actually a very good question. What's the good of being able to
filter is we can't process the filtered data. See SUBTOTAL in Excel Help.
--
Gary''s Student - gsnu200789


"RHino56" wrote:

I have a sheet to track hours of employees for a project. I used the auto-sum
to total the hours range of cells. I would like to format a separate
calculation to total only those cells displayed in a filtered range. As I
change the filter, the total should again include on the new visable cells in
the range. Help!
--
RHino56

sum of visable cells in filtered range
 

You should be able to put the SUBTOTAL function in another column.
--
Gary''s Student - gsnu200789


"RHino56" wrote:

Thanks, Gary's Student. The final value is what I was looking for but, is
there any way to have this subtotal in a dedicated cell location instead of
adding it to the bottom of the list? Now the subtotal is also added into the
auto-sum value for the column. I am using MS Ofc 2003 SP3.
RHino56


"Gary''s Student" wrote:

This is actually a very good question. What's the good of being able to
filter is we can't process the filtered data. See SUBTOTAL in Excel Help.
--
Gary''s Student - gsnu200789


"RHino56" wrote:

I have a sheet to track hours of employees for a project. I used the auto-sum
to total the hours range of cells. I would like to format a separate
calculation to total only those cells displayed in a filtered range. As I
change the filter, the total should again include on the new visable cells in
the range. Help!
--
RHino56

sum of visable cells in filtered range
 

That worked fine. Is there anyway to hide the individual totals when
displaying "all" in the filtered range? If I select a filter group, the page
displays no individual totals, only the grand total. When "all" is selected
there is a line added between each normal line with Total and then the Grand
Total at the bottom.
--
RHino56


"Gary''s Student" wrote:

You should be able to put the SUBTOTAL function in another column.
--
Gary''s Student - gsnu200789


"RHino56" wrote:

Thanks, Gary's Student. The final value is what I was looking for but, is
there any way to have this subtotal in a dedicated cell location instead of
adding it to the bottom of the list? Now the subtotal is also added into the
auto-sum value for the column. I am using MS Ofc 2003 SP3.
RHino56


"Gary''s Student" wrote:

This is actually a very good question. What's the good of being able to
filter is we can't process the filtered data. See SUBTOTAL in Excel Help.
--
Gary''s Student - gsnu200789


"RHino56" wrote:

I have a sheet to track hours of employees for a project. I used the auto-sum
to total the hours range of cells. I would like to format a separate
calculation to total only those cells displayed in a filtered range. As I
change the filter, the total should again include on the new visable cells in
the range. Help!
--
RHino56

sum of visable cells in filtered range
 

Instead of having a SUM formula in your Grand Total row, make this
SUBTOTAL(9, ...). This will give you a grand total when no filter is
being applied, but will then give you the total of the filtered rows
when required.

I would also suggest that you put these totals on the top row of your
sheet, above the headings where the filter arrows occur, and then use
Window | Freeze Panes, so that the totals are always visible if you
scroll down the sheet (and you don't need to scroll to see the
totals).

Hope this helps.

Pete

On Jun 1, 9:04*pm, RHino56 wrote:
That worked fine. Is there anyway to hide the individual totals when
displaying "all" in the filtered range? If I select a filter group, the page
displays no individual totals, only the grand total. When "all" is selected
there is a line added between each normal line with Total and then the Grand
Total at the bottom.
--
RHino56



"Gary''s Student" wrote:
You should be able to *put the SUBTOTAL function in another column.
--
Gary''s Student - gsnu200789

"RHino56" wrote:

Thanks, Gary's Student. The final value is what I was looking for but, is
there any way to have this subtotal in a dedicated cell location instead of
adding it to the bottom of the list? Now the subtotal is also added into the
auto-sum value for the column. I am using MS Ofc 2003 SP3.
RHino56

"Gary''s Student" wrote:

This is actually a very good question. *What's the good of being able to
filter is we can't process the filtered data. *See SUBTOTAL in Excel Help.
--
Gary''s Student - gsnu200789

"RHino56" wrote:

I have a sheet to track hours of employees for a project. I used the auto-sum
to total the hours range of cells. I would like to format a separate
calculation to total only those cells displayed in a filtered range. As I
change the filter, the total should again include on the new visable cells in
the range. Help!
--
RHino56- Hide quoted text -

- Show quoted text -

sum of visable cells in filtered range
 

Stick the SUBTOTAL formula in the top row of your sheet in any column.


Gord Dibben MS Excel MVP

On Sun, 1 Jun 2008 11:29:00 -0700, RHino56
wrote:

Thanks, Gary's Student. The final value is what I was looking for but, is
there any way to have this subtotal in a dedicated cell location instead of
adding it to the bottom of the list? Now the subtotal is also added into the
auto-sum value for the column. I am using MS Ofc 2003 SP3.
RHino56


"Gary''s Student" wrote:

This is actually a very good question. What's the good of being able to
filter is we can't process the filtered data. See SUBTOTAL in Excel Help.
--
Gary''s Student - gsnu200789


"RHino56" wrote:

I have a sheet to track hours of employees for a project. I used the auto-sum
to total the hours range of cells. I would like to format a separate
calculation to total only those cells displayed in a filtered range. As I
change the filter, the total should again include on the new visable cells in
the range. Help!
--
RHino56

sum of visable cells in filtered range
 

Sounds like you're using DataSubtotal and not the SUBTOTAL function which is

=SUBTOTAL(9,range) entered in a cell.


Gord

On Sun, 1 Jun 2008 13:04:00 -0700, RHino56
wrote:

That worked fine. Is there anyway to hide the individual totals when
displaying "all" in the filtered range? If I select a filter group, the page
displays no individual totals, only the grand total. When "all" is selected
there is a line added between each normal line with Total and then the Grand
Total at the bottom.