Sum of visible cells only
 

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?

Sum of visible cells only
 

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.

"Stgeorge" wrote:

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?

Sum of visible cells only
 

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)

"Stgeorge" wrote in message
...
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?

Sum of visible cells only
 

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.

"Bob Phillips" wrote:

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)

"Stgeorge" wrote in message
...
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?

Sum of visible cells only
 

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.

Stgeorge Wrote:
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.

"Bob Phillips" wrote:

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)

"Stgeorge" wrote in message
...
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?





--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=573876

Sum of visible cells only
 

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

Stgeorge wrote:
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.

"Bob Phillips" wrote:

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)

"Stgeorge" wrote in message
...
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?

Sum of visible cells only
 

"Pete_UK" wrote in message
ups.com...
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.

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

Sum of visible cells only
 

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

Bob Phillips wrote:
"Pete_UK" wrote in message
ups.com...
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.

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

Sum of visible cells only
 

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)

"Pete_UK" wrote in message
ups.com...
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

Bob Phillips wrote:
"Pete_UK" wrote in message
ups.com...
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.

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

Sum of visible cells only
 

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.

"Pete_UK" wrote:

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

Bob Phillips wrote:
"Pete_UK" wrote in message
ups.com...
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.

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

Sum of visible cells only
 

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

Bob Phillips wrote:
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)

"Pete_UK" wrote in message
ups.com...
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

Bob Phillips wrote:
"Pete_UK" wrote in message
ups.com...
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.

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

Sum of visible cells only
 

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,..)


"Stgeorge" wrote:

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.

"Pete_UK" wrote:

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

Bob Phillips wrote:
"Pete_UK" wrote in message
ups.com...
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.

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