Adding up range of cells exclude hide cells

[Len]

Hi,

How to set excel function or formula such as SUM function to add up
the range of selected cells which gives the result excludes hide
cells, particularly for long range of cells in a column ?


E.g.

In this case, the result excludes hide cells will be 10,086.40 ( ie
A1+A4+A5+A6+A8+A9 ) and not 10,884.00 ( ie adding up cells from A1 to
A10 )
A
1 4,084.20
2 300.00 ( Hide cell )
3 450.00 ( Hide cell )
4 3,965.00
5 200.00
6 50.00
7 47.00 ( Hide cell )
8 1,037.20
9 750.00
10 0.60 ( Hide cell )
---------------------
10,086.40 ( Sum up exclude hide cells )
==========

Please help, thanks

Regards
Lenard

 

[Norman Jones]

Hi Len,

If the cells were hidden as the result of a filter (or if yuo are using a
recent version of Excel, try the SubTotal function:

=SubTotal(9, A1:A10)

See Excel help for nore details.

 

[Len]

Hi Len,

If the cells were hidden as the result of a filter (or if yuo are using a
recent version of Excel, try the SubTotal function:

        =SubTotal(9, A1:A10)

See Excel help for nore details.

---
Regards.
Norman









- Show quoted text -

Hi Norman,

Ya....... from excel help. Thanks for your advice

Regards
Lenard

 

[Len]

Hi Norman,

Ya....... from excel help.  Thanks for your advice

Regards
Lenard- Hide quoted text -

- Show quoted text -

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

 

[Norman Jones]

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,

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

 

[Len]

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.

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

 

[Norman Jones]

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

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]

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
<=============

Hi Norman,

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

Regards
Len