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 |
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 |
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 |
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 |
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 |
Adding up range of cells exclude hide cells
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 - |
All times are GMT +1. The time now is 03:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com