How to produce totals of a filtered column?

[StargateFanFromWork]

a. The only way I'd know to do this would be to add an extra row to the
header row of which one cell would add up the totals of a column, filtered
and unfiltered.

b. I imagine, though, from using filtering in other workbooks, that it will
require trickier coding to handle only the visible cells(?).




So my questions are:

1. Is there not a better way than what I have managed to think of re having
a "totals" cell when filtering is involved? If so, how would one do this?

The filtering in this case is based on a YES and NO answer in column J.
Incidentally, the left/right margins are as tight as they can really be
since they're already at 0.25 inches so I can't add an column whatsoever.
That's how I managed to make a totals cell horizontally rather than
vertically for a workbook that could be printed out sorted in various
different ways. The totals cell in the header row always remained in the
same spot because of its location, so that's why that worked there. Not
ideal because the printout showed a darker empty spot below that header
cell, but it did the job.

2. The costs column is located in column J. Does anyone know of a code
that works with filtering done on that column that produces totals on what
is visible?



Thanks. Any comments welcome. I'm not very knowledgeable re vb though I'm
very good at modifying code once I've used it successfully.

Also, it's difficult to describe what one is trying to do, I've found, since
one doesn't know in advance the correct terminology. So if anything needs
clarifying, pls ask me and I'll try to explain whatever point is not clear.

 

[Gord Dibben]

Use the SUBTOTAL function

=SUBTOTAL(9,range)

The "9" says Sum only the visible cells.

For more options see the SUBTOTAL fucnction in Help


Gord Dibben MS Excel MVP