Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add up column of numbers without including hidden rows?
I am trying to add up a column of numbers but I have hidden rows in the
spreadsheet and don't want to include them in the sum. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add up column of numbers without including hidden rows?
On Thu, 31 Jan 2008 20:33:01 -0800, laserhallam
wrote: I am trying to add up a column of numbers but I have hidden rows in the spreadsheet and don't want to include them in the sum. How were the rows hidden? What version of Excel? In Excel 2003 (and I assume later), you can use the function: =SUBTOTAL(109,rng) where range is the reference to the cells. Hidden rows will not be included in the sum. For earlier versions of Excel, SUBTOTAL(9, rng) will not include rows that were hidden as the result of a filter. (I'm really not sure about Excel 2002, though). --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add up column of numbers without including hidden rows?
If you hide rows manually, the SUBTOTAL function ignores any values hidden
by a filter. The SUBTOTAL function with function_num of 1 to 11 includes rows hidden manually with hide rows. The SUBTOTAL function with function_num of 101-111 excludes rows hidden manually with hide rows. How do I know this? I read the help file. :) Tyro "laserhallam" wrote in message ... I am trying to add up a column of numbers but I have hidden rows in the spreadsheet and don't want to include them in the sum. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I add up column of numbers without including hidden rows?
"Ron Rosenfeld" wrote in message
... On Thu, 31 Jan 2008 20:33:01 -0800, laserhallam wrote: I am trying to add up a column of numbers but I have hidden rows in the spreadsheet and don't want to include them in the sum. How were the rows hidden? What version of Excel? In Excel 2003 (and I assume later), you can use the function: =SUBTOTAL(109,rng) where range is the reference to the cells. Hidden rows will not be included in the sum. For earlier versions of Excel, SUBTOTAL(9, rng) will not include rows that were hidden as the result of a filter. (I'm really not sure about Excel 2002, though). --ron In Excel 2002 and earlier, SUBTOTAL(n,rng) will only exclude those rows that are hidden by using a filter. The 100 series arguments were added in Excel 2003 and are still available in Excel 2007. -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum of a column excluding hidden rows | Excel Worksheet Functions | |||
serial numbers with hidden rows | Excel Worksheet Functions | |||
How do I do a sumif function not including hidden rows? | Excel Worksheet Functions | |||
Counting numbers in a column without including others | Excel Worksheet Functions | |||
Calculating without including Hidden Cells | Excel Discussion (Misc queries) |