using SUBTOTAL() on rows that have been hidden

This additional feature of =subtotal() was added in xl2003.



N Harkawat wrote:
>
> use 101 instead of 1 to ignore hidden values
> =subtotal(101,a2:a25)
>
> "doco" <perdedor@centurytel.net> wrote in message
> news:%23o%23J302aFHA.3120@TK2MSFTNGP12.phx.gbl...
> > Formulas like SUBTOTAL(1,A2:A25) or a more complex cousin like
> > {=MEDIAN(IF(SUBTOTAL(3,OFFSET($U$2:$U$30,ROW($U$2:$U$30)-MIN(ROW($U$2:$U$30)),0,1)),$U$2:$U$30))}
> > work really great if one is using AutoFilter.
> >
> > However I noticed that if one merely needs to 'hide' a single row by using
> > Format | Row | Hide the function does not do what it should or what is
> > expected; that is to calculate only visible rows.
> >
> > Is this a bug or do I need something else?
> >
> > doco
> >

--

Dave Peterson

use 101 instead of 1 to ignore hidden values
=subtotal(101,a2:a25)

"doco" <perdedor@centurytel.net> wrote in message
news:%23o%23J302aFHA.3120@TK2MSFTNGP12.phx.gbl...
> Formulas like SUBTOTAL(1,A2:A25) or a more complex cousin like
> {=MEDIAN(IF(SUBTOTAL(3,OFFSET($U$2:$U$30,ROW($U$2:$U$30)-MIN(ROW($U$2:$U$30)),0,1)),$U$2:$U$30))}
> work really great if one is using AutoFilter.
>
> However I noticed that if one merely needs to 'hide' a single row by using
> Format | Row | Hide the function does not do what it should or what is
> expected; that is to calculate only visible rows.
>
> Is this a bug or do I need something else?
>
> doco
>

Formulas like SUBTOTAL(1,A2:A25) or a more complex cousin like
{=MEDIAN(IF(SUBTOTAL(3,OFFSET($U$2:$U$30,ROW($U$2:$U$30)-MIN(ROW($U$2:$U$30)),0,1)),$U$2:$U$30))}
work really great if one is using AutoFilter.

However I noticed that if one merely needs to 'hide' a single row by using
Format | Row | Hide the function does not do what it should or what is
expected; that is to calculate only visible rows.

Is this a bug or do I need something else?

doco

use 101 instead of 1 to ignore hidden values
=subtotal(101,a2:a25)

"doco" <perdedor@centurytel.net> wrote in message
news:%23o%23J302aFHA.3120@TK2MSFTNGP12.phx.gbl...
> Formulas like SUBTOTAL(1,A2:A25) or a more complex cousin like
> {=MEDIAN(IF(SUBTOTAL(3,OFFSET($U$2:$U$30,ROW($U$2:$U$30)-MIN(ROW($U$2:$U$30)),0,1)),$U$2:$U$30))}
> work really great if one is using AutoFilter.
>
> However I noticed that if one merely needs to 'hide' a single row by using
> Format | Row | Hide the function does not do what it should or what is
> expected; that is to calculate only visible rows.
>
> Is this a bug or do I need something else?
>
> doco
>

This additional feature of =subtotal() was added in xl2003.



N Harkawat wrote:
>
> use 101 instead of 1 to ignore hidden values
> =subtotal(101,a2:a25)
>
> "doco" <perdedor@centurytel.net> wrote in message
> news:%23o%23J302aFHA.3120@TK2MSFTNGP12.phx.gbl...
> > Formulas like SUBTOTAL(1,A2:A25) or a more complex cousin like
> > {=MEDIAN(IF(SUBTOTAL(3,OFFSET($U$2:$U$30,ROW($U$2:$U$30)-MIN(ROW($U$2:$U$30)),0,1)),$U$2:$U$30))}
> > work really great if one is using AutoFilter.
> >
> > However I noticed that if one merely needs to 'hide' a single row by using
> > Format | Row | Hide the function does not do what it should or what is
> > expected; that is to calculate only visible rows.
> >
> > Is this a bug or do I need something else?
> >
> > doco
> >

--

Dave Peterson