| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding up range of cells exclude hide cells
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding up range of cells exclude hide cells
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding up range of cells exclude hide cells
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding up range of cells exclude hide cells
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding up range of cells exclude hide cells
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to exclude certain cells in a range | Excel Worksheet Functions | |||
| How to exclude certain cells from a range? | Excel Programming | |||
| How to use macros to copy a range of cells which can exclude some cells which I didn't want to be copied? | Excel Worksheet Functions | |||
| Exclude blank cells from a range? | Excel Programming | |||
| Adding colour to a range of cells based on one of the cells v... | Excel Discussion (Misc queries) | |||
Hybrid Mode
