Sum of visible cells only

S

Stgeorge

I have a large spreadsheet that I am using multiple drop downs in to sort for
different scenarios. I do not want to use the general data subtotal command
but each time I sort I want to get a total for the visible cells - how do you
do this?
 
J

jchen

Do, a sum from the top cell to the very last cell at bottom. The result will
be based on the visible cells automatically. And the result will change
correspondly when you change the filter. Just make sure don't filter out the
the result.
 
B

Bob Phillips

Why do you not want to use SUBTOTAL, it does what you want?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
S

Stgeorge

Thanks for repying to this.

If I filter the data I can get a subtotal at the bottom - however when I
filter using another criteria the subtotal seems to stay associated with the
original data and gives me an incorrect summation for the new visible data.
 
S

starguy

your workbooks calculation may be set to manual.
go to Tools > Options > Calculation tab > and click Automatic and Ok
and apply different filters to check for the answers.
 
P

Pete_UK

Apply a filter to your data, then move to the bottom of the data. Leave
at least ONE blank row (ensure that the filter is not active for this
row, by checking the colour of the row indicator), and click the SUM
icon in the appropriate cell below this blank row - this will
automatically convert to SUBTOTAL(9, ...) and as it is outside the
range used by the filter it will always be available.

I find it more convenient to put the summary totals on the top row -
that way they are always visible immediately you choose your filter,
rather than have to move down to the bottom to see the values. Insert a
new row 1, go down to your summary row and cut/paste it to the top row
(above your filters).

Hope this helps.

Pete
 
B

Bob Phillips

Pete_UK said:
Apply a filter to your data, then move to the bottom of the data. Leave
at least ONE blank row (ensure that the filter is not active for this
row, by checking the colour of the row indicator), and click the SUM
icon in the appropriate cell below this blank row - this will
automatically convert to SUBTOTAL(9, ...) and as it is outside the
range used by the filter it will always be available.
Click to expand...

So there you go! I didn't know that. Can't see me ever using it, but at
least I know now <G>
 
P

Pete_UK

I discovered this in the dim and distant past, as I now put totals on
the top row. It seemed that Excel maintains an "active range" over
which the filters apply, and if you add anything to any cell in the
blank row below this, then the filter range gets extended.

Pete
 
B

Bob Phillips

I also tend to put totals at the top, far more robust. That Excel is damn
smart isn't it?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
S

Stgeorge

Thanks to all - finally got this figured out today. I actually took the
subtotal formula that it generated for me and extended it out over the entire
range rather than the one it gave me and it seems to adjusted for the filter.
Works very well.

Again thanks, I can stop scratching my rapidly balding head now.
 
P

Pete_UK

It's handy to know if you copy some new data under data in a sheet
which already has filters set up - if you do not leave a blank row then
the filter range will automatically include the extra records. Pity it
doesn't automatically extend named ranges which you might have set up
with the first lot of data (unless they are dynamic) - I suppose we get
used to these inconsistencies !! <bg>

Pete
 
J

jaysin

Also, don't forget that if you use 109 instead of 9 for the SUM, this will
ensure you will only get visible cells. so.. =SUBTOTAL(109,..)
 
Top