Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a column excluding hidden rows
How do I sum many rows within a column but exclude hidden colums from the
calculation? Thank you! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a column excluding hidden rows
Sorry, I meant exclude hidden rows from the calc, not columns.
"mnwild1" wrote: How do I sum many rows within a column but exclude hidden colums from the calculation? Thank you! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a column excluding hidden rows
Which version of Excel and how are the rows hidden?
Excel 2003 has added a function that subtotals manually hidden and/or filtered rows. If 2003 use the formula =SUBTOTAL(109,rangetosum) to sum all hidden rows no matter the method. In earlier versions if rows are hidden by Filtering then use this. =SUBTOTAL(9,rangetosum) If an earlier version and rows are hidden manually select the range and F5SpecialVisible cells only and OK. Copy/paste to an unused range and sum them there. Gord Dibben MS Excel MVP On Thu, 21 Jun 2007 19:40:01 -0700, mnwild1 wrote: How do I sum many rows within a column but exclude hidden colums from the calculation? Thank you! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a column excluding hidden rows
Hello Gord.
I am using Excel 2007. I have manually hidden the rows I want to exclude from the calculation but when I use the autosum, it counts the hidden rows. How can I sum the column but not count the hidden rows? Thank you!! "Gord Dibben" wrote: Which version of Excel and how are the rows hidden? Excel 2003 has added a function that subtotals manually hidden and/or filtered rows. If 2003 use the formula =SUBTOTAL(109,rangetosum) to sum all hidden rows no matter the method. In earlier versions if rows are hidden by Filtering then use this. =SUBTOTAL(9,rangetosum) If an earlier version and rows are hidden manually select the range and F5SpecialVisible cells only and OK. Copy/paste to an unused range and sum them there. Gord Dibben MS Excel MVP On Thu, 21 Jun 2007 19:40:01 -0700, mnwild1 wrote: How do I sum many rows within a column but exclude hidden colums from the calculation? Thank you! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a column excluding hidden rows
I have a kludge work-around for versions prior to Excel 2003 but it requires
the use of a helper column. A hidden row has a height property of 0 so all you need to do is sum the rows with a height 0. Assume the range of interest is A1:A10 but several of those rows are hidden. Create this named formula: InsertNameDefine Name: Height Refers to: =GET.CELL(17,INDIRECT("RC",FALSE)) OK Enter this formula in B1 and copy down to B10: =Height It will also be copied to the hidden rows. Now, to get the sum of only the visible rows in A1:A10 =SUMIF(B1:B10,"0",A1:A10) Note that hidding or unhidding rows/columns does not trigger a calculation. If all rows are visible and then you hide some the formula will not show the correct result until a calculation takes place. You can either wait for some other action that triggers an automatic calculation or you can hit F9 to force a calculation. Like I said, kludge, but it's an option! Biff "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Which version of Excel and how are the rows hidden? Excel 2003 has added a function that subtotals manually hidden and/or filtered rows. If 2003 use the formula =SUBTOTAL(109,rangetosum) to sum all hidden rows no matter the method. In earlier versions if rows are hidden by Filtering then use this. =SUBTOTAL(9,rangetosum) If an earlier version and rows are hidden manually select the range and F5SpecialVisible cells only and OK. Copy/paste to an unused range and sum them there. Gord Dibben MS Excel MVP On Thu, 21 Jun 2007 19:40:01 -0700, mnwild1 wrote: How do I sum many rows within a column but exclude hidden colums from the calculation? Thank you! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a column excluding hidden rows
Thank you for responding. Do you know of a way to do this with Excel 2007?
"T. Valko" wrote: I have a kludge work-around for versions prior to Excel 2003 but it requires the use of a helper column. A hidden row has a height property of 0 so all you need to do is sum the rows with a height 0. Assume the range of interest is A1:A10 but several of those rows are hidden. Create this named formula: InsertNameDefine Name: Height Refers to: =GET.CELL(17,INDIRECT("RC",FALSE)) OK Enter this formula in B1 and copy down to B10: =Height It will also be copied to the hidden rows. Now, to get the sum of only the visible rows in A1:A10 =SUMIF(B1:B10,"0",A1:A10) Note that hidding or unhidding rows/columns does not trigger a calculation. If all rows are visible and then you hide some the formula will not show the correct result until a calculation takes place. You can either wait for some other action that triggers an automatic calculation or you can hit F9 to force a calculation. Like I said, kludge, but it's an option! Biff "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Which version of Excel and how are the rows hidden? Excel 2003 has added a function that subtotals manually hidden and/or filtered rows. If 2003 use the formula =SUBTOTAL(109,rangetosum) to sum all hidden rows no matter the method. In earlier versions if rows are hidden by Filtering then use this. =SUBTOTAL(9,rangetosum) If an earlier version and rows are hidden manually select the range and F5SpecialVisible cells only and OK. Copy/paste to an unused range and sum them there. Gord Dibben MS Excel MVP On Thu, 21 Jun 2007 19:40:01 -0700, mnwild1 wrote: How do I sum many rows within a column but exclude hidden colums from the calculation? Thank you! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a column excluding hidden rows
Try "Gord Dibben" formula will work your manually hidden rows with your XL2007
=SUBTOTAL(109,your data range) "mnwild1" wrote: Hello Gord. I am using Excel 2007. I have manually hidden the rows I want to exclude from the calculation but when I use the autosum, it counts the hidden rows. How can I sum the column but not count the hidden rows? Thank you!! "Gord Dibben" wrote: Which version of Excel and how are the rows hidden? Excel 2003 has added a function that subtotals manually hidden and/or filtered rows. If 2003 use the formula =SUBTOTAL(109,rangetosum) to sum all hidden rows no matter the method. In earlier versions if rows are hidden by Filtering then use this. =SUBTOTAL(9,rangetosum) If an earlier version and rows are hidden manually select the range and F5SpecialVisible cells only and OK. Copy/paste to an unused range and sum them there. Gord Dibben MS Excel MVP On Thu, 21 Jun 2007 19:40:01 -0700, mnwild1 wrote: How do I sum many rows within a column but exclude hidden colums from the calculation? Thank you! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a column excluding hidden rows
Hi
Gord and TM have both told you the method. Have you tried it? =SUBTOTAL(109,A1:A1000) will add all of the values for unhidden rows in the range A1:A1000 -- Regards Roger Govier "mnwild1" wrote in message ... Thank you for responding. Do you know of a way to do this with Excel 2007? "T. Valko" wrote: I have a kludge work-around for versions prior to Excel 2003 but it requires the use of a helper column. A hidden row has a height property of 0 so all you need to do is sum the rows with a height 0. Assume the range of interest is A1:A10 but several of those rows are hidden. Create this named formula: InsertNameDefine Name: Height Refers to: =GET.CELL(17,INDIRECT("RC",FALSE)) OK Enter this formula in B1 and copy down to B10: =Height It will also be copied to the hidden rows. Now, to get the sum of only the visible rows in A1:A10 =SUMIF(B1:B10,"0",A1:A10) Note that hidding or unhidding rows/columns does not trigger a calculation. If all rows are visible and then you hide some the formula will not show the correct result until a calculation takes place. You can either wait for some other action that triggers an automatic calculation or you can hit F9 to force a calculation. Like I said, kludge, but it's an option! Biff "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Which version of Excel and how are the rows hidden? Excel 2003 has added a function that subtotals manually hidden and/or filtered rows. If 2003 use the formula =SUBTOTAL(109,rangetosum) to sum all hidden rows no matter the method. In earlier versions if rows are hidden by Filtering then use this. =SUBTOTAL(9,rangetosum) If an earlier version and rows are hidden manually select the range and F5SpecialVisible cells only and OK. Copy/paste to an unused range and sum them there. Gord Dibben MS Excel MVP On Thu, 21 Jun 2007 19:40:01 -0700, mnwild1 wrote: How do I sum many rows within a column but exclude hidden colums from the calculation? Thank you! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a column excluding hidden rows
Biff,
This is a cool trick. Do you have any pointer to documentation of GET.CELL? Kostis On Jun 22, 6:29 am, "T. Valko" wrote: I have a kludge work-around for versions prior to Excel 2003 but it requires the use of a helper column. A hidden row has a height property of 0 so all you need to do is sum the rows with a height 0. Assume the range of interest is A1:A10 but several of those rows are hidden. Create this named formula: InsertNameDefine Name: Height Refers to: =GET.CELL(17,INDIRECT("RC",FALSE)) OK Enter this formula in B1 and copy down to B10: =Height It will also be copied to the hidden rows. Now, to get the sum of only the visible rows in A1:A10 =SUMIF(B1:B10,"0",A1:A10) Note that hidding or unhidding rows/columns does not trigger a calculation. If all rows are visible and then you hide some the formula will not show the correct result until a calculation takes place. You can either wait for some other action that triggers an automatic calculation or you can hit F9 to force a calculation. Like I said, kludge, but it's an option! Biff "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Which version of Excel and how are the rows hidden? Excel 2003 has added a function that subtotals manually hidden and/or filtered rows. If 2003 use the formula =SUBTOTAL(109,rangetosum) to sum all hidden rows no matter the method. In earlier versions if rows are hidden by Filtering then use this. =SUBTOTAL(9,rangetosum) If an earlier version and rows are hidden manually select the range and F5SpecialVisible cells only and OK. Copy/paste to an unused range and sum them there. Gord Dibben MS Excel MVP On Thu, 21 Jun 2007 19:40:01 -0700, mnwild1 wrote: How do I sum many rows within a column but exclude hidden colums from the calculation? Thank you! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a column excluding hidden rows
Don't use the Autosum Button.
Gord On Thu, 21 Jun 2007 20:14:00 -0700, mnwild1 wrote: Hello Gord. I am using Excel 2007. I have manually hidden the rows I want to exclude from the calculation but when I use the autosum, it counts the hidden rows. How can I sum the column but not count the hidden rows? Thank you!! "Gord Dibben" wrote: Which version of Excel and how are the rows hidden? Excel 2003 has added a function that subtotals manually hidden and/or filtered rows. If 2003 use the formula =SUBTOTAL(109,rangetosum) to sum all hidden rows no matter the method. In earlier versions if rows are hidden by Filtering then use this. =SUBTOTAL(9,rangetosum) If an earlier version and rows are hidden manually select the range and F5SpecialVisible cells only and OK. Copy/paste to an unused range and sum them there. Gord Dibben MS Excel MVP On Thu, 21 Jun 2007 19:40:01 -0700, mnwild1 wrote: How do I sum many rows within a column but exclude hidden colums from the calculation? Thank you! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of a column excluding hidden rows
Here's a link to the compiled help file that you can install:
http://tinyurl.com/ydght4 I have it as a text file that I got from Bob Phillips a few years ago: http://cjoint.com/?gxaoQpmgiy Biff "vezerid" wrote in message oups.com... Biff, This is a cool trick. Do you have any pointer to documentation of GET.CELL? Kostis On Jun 22, 6:29 am, "T. Valko" wrote: I have a kludge work-around for versions prior to Excel 2003 but it requires the use of a helper column. A hidden row has a height property of 0 so all you need to do is sum the rows with a height 0. Assume the range of interest is A1:A10 but several of those rows are hidden. Create this named formula: InsertNameDefine Name: Height Refers to: =GET.CELL(17,INDIRECT("RC",FALSE)) OK Enter this formula in B1 and copy down to B10: =Height It will also be copied to the hidden rows. Now, to get the sum of only the visible rows in A1:A10 =SUMIF(B1:B10,"0",A1:A10) Note that hidding or unhidding rows/columns does not trigger a calculation. If all rows are visible and then you hide some the formula will not show the correct result until a calculation takes place. You can either wait for some other action that triggers an automatic calculation or you can hit F9 to force a calculation. Like I said, kludge, but it's an option! Biff "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Which version of Excel and how are the rows hidden? Excel 2003 has added a function that subtotals manually hidden and/or filtered rows. If 2003 use the formula =SUBTOTAL(109,rangetosum) to sum all hidden rows no matter the method. In earlier versions if rows are hidden by Filtering then use this. =SUBTOTAL(9,rangetosum) If an earlier version and rows are hidden manually select the range and F5SpecialVisible cells only and OK. Copy/paste to an unused range and sum them there. Gord Dibben MS Excel MVP On Thu, 21 Jun 2007 19:40:01 -0700, mnwild1 wrote: How do I sum many rows within a column but exclude hidden colums from the calculation? Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting non-blank cells in a column, excluding hidden rows | Excel Worksheet Functions | |||
Need to sum columns, excluding hidden ones - like 'subtotal' for r | Excel Discussion (Misc queries) | |||
I need my Hidden Rows to stay hidden when I print the sheet. | Excel Discussion (Misc queries) | |||
paste excel sheet excluding hidden rows | Excel Discussion (Misc queries) | |||
Column Auto Width and Hidden Rows | Excel Discussion (Misc queries) |