Sum of visible cells only

[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?

[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.

"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?

[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)

"Stgeorge" <[email protected]> wrote in message
news:[email protected]...
> 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?

[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.

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

[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

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> >
> >
> >

[Bob Phillips]

"Pete_UK" <[email protected]> wrote in message
news:[email protected]...
> 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>

[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

Bob Phillips wrote:
> "Pete_UK" <[email protected]> wrote in message
> news:[email protected]...
> > 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>

[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)

"Pete_UK" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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>
>

[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.

"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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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>
>
>

[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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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>
> >