Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
How do I omit hidden data from autosum calculations?
|
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi
Use SUBTOTAL() to calculate the sum (At least it works when rows are hidded using autofilter - I'm not sure does it work when rows are simply hidden). Arvi Laanemets "wolfthrone" wrote in message ... How do I omit hidden data from autosum calculations? |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
If the data is hidden by virtue of a filtering operation, rather than simply
hiding the rows, you can substitute =SUBTOTAL(9,range) for =SUM(range) The SUBTOTAL() fucntion will ignore rows that the filter hides "wolfthrone" wrote: How do I omit hidden data from autosum calculations? |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I am using 2000 so can't test this, but starting with 2003 (I believe) you
can also filter hidden rows not hidden by filtering by adding to whatever number you are using in the subtotal formula. i.e. =SUBTOTAL(9,range) would become =SUBTOTAL(109,range) -- Kevin Vaughn "Duke Carey" wrote: If the data is hidden by virtue of a filtering operation, rather than simply hiding the rows, you can substitute =SUBTOTAL(9,range) for =SUM(range) The SUBTOTAL() fucntion will ignore rows that the filter hides "wolfthrone" wrote: How do I omit hidden data from autosum calculations? |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi Arvin
For your information, in XL2003 you can use sub-total with any hidden rows, as well as those hidden with filter. You just use 100 added to the normal Subtotal parameter e.g =SUBTOTAL(109,A5:A1000) as opposed to =SUBTOTAL(9,A5:A1000) -- Regards Roger Govier "Arvi Laanemets" wrote in message ... Hi Use SUBTOTAL() to calculate the sum (At least it works when rows are hidded using autofilter - I'm not sure does it work when rows are simply hidden). Arvi Laanemets "wolfthrone" wrote in message ... How do I omit hidden data from autosum calculations? |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
:-)
Btw., as I use Xl2000, all additional features of any versions of Xl200* are beyound my experience. Arvi Laanemets "Roger Govier" wrote in message ... Sorry Arvi An extraneous "n" crept onto the end of your name. Can't even blame "fat fingers" for that one<bg Maybe it was autocorrect and I didn't notice. -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Arvin For your information, in XL2003 you can use sub-total with any hidden rows, as well as those hidden with filter. You just use 100 added to the normal Subtotal parameter e.g =SUBTOTAL(109,A5:A1000) as opposed to =SUBTOTAL(9,A5:A1000) -- Regards Roger Govier "Arvi Laanemets" wrote in message ... Hi Use SUBTOTAL() to calculate the sum (At least it works when rows are hidded using autofilter - I'm not sure does it work when rows are simply hidden). Arvi Laanemets "wolfthrone" wrote in message ... How do I omit hidden data from autosum calculations? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Charts not recognizing source data if original linked data is changed. | Charts and Charting in Excel | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
sort column data with hidden columns - excel 2003 | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |