sum of visable cells in filtered range

[RHino56]

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!

 

[Gary''s Student]

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.

 

[RHino56]

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]

You should be able to put the SUBTOTAL function in another column.

 

[RHino56]

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.

 

[Pete_UK]

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

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Sounds like you're using Data>Subtotal and not the SUBTOTAL function which is

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


Gord