sum of visable cells in filtered range

  • Thread starter Thread starter RHino56
  • Start date Start date
R

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!
 
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.
 
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
 
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.
 
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
 
Stick the SUBTOTAL formula in the top row of your sheet in any column.


Gord Dibben MS Excel MVP
 
Sounds like you're using Data>Subtotal and not the SUBTOTAL function which is

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


Gord
 
Back
Top