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

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


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

On May 22, 12:59*am, "Norman Jones"
wrote:
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

"Len" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Hi Norman,

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

Regards
Lenard

On May 22, 9:30*am, Len wrote:
On May 22, 12:59*am, "Norman Jones"
wrote:





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

"Len" wrote in message

...

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- Hide quoted text -

- Show quoted text -

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

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


"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

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 -