On May 25, 9:37*pm, "Norman Jones"
wrote:
Hi Len,

Until xl2002 (if I recollect correctly) the SubTotal
function excluded filtered values and offered
function values between 1 and 11; with the advent
of xl2002, additional function numbers 101-111
were added which applied the same function as
the existing function numbers 1-111, *but which
excluded filtered and hidden values.

From Excel 2007 help:

=============
Function_num * is the number 1 to 11 (includes hidden values) or 101 to 111
(ignores hidden values) that specifies which function to use in calculating
subtotals within a list.

Function_num * * * * *Function_num
(includes hidden * * *(ignores hidden * * * * Function
* * *values) * * * * * * * * * * * values)
* * 1 * * * * * * * * * * * * * * * * 101 * * * * * * * * AVERAGE
* * 2 * * * * * * * * * * * * * * * * 102 * * * * * * * * COUNT
* * 3 * * * * * * * * * * * * * * * * 103 * * * * * * * * COUNTA
* * 4 * * * * * * * * * * * * * * * * 104 * * * * * * * * MAX
* * 5 * * * * * * * * * * * * * * * * 105 * * * * * * * * MIN
* * 6 * * * * * * * * * * * * * * * * 106 * * * * * * * * PRODUCT
* * 7 * * * * * * * * * * * * * * * * 107 * * * * * * * * STDEV
* * 8 * * * * * * * * * * * * * * * * 108 * * * * * * * * STDEVP
* * 9 * * * * * * * * * * * * * * * * 109 * * * * * * * * SUM
* *10 * * * * * * * * * * * * * * * *110 * * * * * * * * VAR
* *11 * * * * * * * * * * * * * * * *111 * * * * * * * * VARP
<=============

---
Regards.
Norman

Hi Norman,

Thanks again for your infor which I think it helps me a lot to make it
clearer

Regards
Len




"Len" wrote in message

...
On May 23, 5:31 pm, "Norman Jones"
wrote:

Hi Len,

If your hidden rows are not hidden by a
filter, try using the formula:

=SUBTOTAL(109,A1:A1)

which will exclude from the sum any hidden
values; the previous formula only excludes
values hidden by a filter.

---
Regards.
Norman

Hi Norman,

Thanks a lot, it works for the values hidden not using filter but in
excel help, the function number only up to 11, there are how many more
function numbers can we use for specific purpose, like in this case
function_num "109" *and where can I refer ?

Regards
Lenard

Regards
Len





"Len" wrote in message

Hi Norman,

After "=SubTotal(9, A1:A10)" is calculated, the result is different
( ie 10,884.00 ) and unable to obtain 10,086.40, am I miss out
anything ?

Regards
Len- Hide quoted text -

- Show quoted text -