Originally SUBTOTAL had those numbers and only ignored values which
were hidden by means of Autofilter. In later versions (I think 2002
onwards) they introduced other numbers with 100 added onto the ones
you quote, and these cause the function to ignore not only filtered-
out values but also values that were hidden manually (as you
describe).

Pete

On Sep 23, 11:47*am, "Alain Dekker"
wrote:
Thanks for those swift replies.

Unfortunately, I left out an important bit of information: I'm using Excel
97 SR-2. When I checked the help on "SUBTOTAL" there was no reference to the
value "102". The help only mentions these values:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

If I try any number higher than "11" for the ref_num parameter, I just get
an error. Is this "102" function something from a later version of Excel and
if so, can I do this in Excel 97?

Thanks again,
Alain

"Stefi" wrote in message

...



=SUBTOTAL(102,A1:A6)
Regards,
Stefi

"Alain Dekker" ezt írta:

Hi,

I set up a new workbook and put the values 1,2,3,4 into cells A1 to A4
respectively. Leave A5 and A6 blank. Now I put the formula
"=count(B1:B6)"
into cell A7 and the values is 4 (ie. 4 non-blank cells).

All well and good.

Now I hide row 3. The value still stays 4. I know this is correct, but is
there another formula that just counts visible, non-blank cells?

Thanks,
Alain- Hide quoted text -

- Show quoted text -