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