Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to not count hidden cells?
I am trying to set up a log, of sorts that will allow me to keep running
totals. I need this to take the numbers out as the rows are hidden (upon completion of the task). Is this a possiblity through any normal excel functions, or is this something that will require a macro? I am running excel 2003. Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to not count hidden cells?
Hi
Have a look at the SUBTOTAL function. In particular, pay attention to which argument you should use. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Jimbob" wrote: I am trying to set up a log, of sorts that will allow me to keep running totals. I need this to take the numbers out as the rows are hidden (upon completion of the task). Is this a possiblity through any normal excel functions, or is this something that will require a macro? I am running excel 2003. Thanks, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to not count hidden cells?
I think I may have worded that wrong. When looking at the subtotal funtion,
I find that it still seems to count items that are filtered out using autofilter. Is there a way not to count the items that are filtered out by autofilter? "Wigi" wrote: Hi Have a look at the SUBTOTAL function. In particular, pay attention to which argument you should use. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Jimbob" wrote: I am trying to set up a log, of sorts that will allow me to keep running totals. I need this to take the numbers out as the rows are hidden (upon completion of the task). Is this a possiblity through any normal excel functions, or is this something that will require a macro? I am running excel 2003. Thanks, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to not count hidden cells?
If you mean sum the total in the hidden cells then try:
=SUM(B2:B21)-SUBTOTAL(9,B2:B21) If you mean count the number of visible cells then use: =SUBTOTAL(2,B2:B21) or =SUBTOTAL(3,B2:B21) Depending on what is in the cells. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Jimbob" wrote in message ... I think I may have worded that wrong. When looking at the subtotal funtion, I find that it still seems to count items that are filtered out using autofilter. Is there a way not to count the items that are filtered out by autofilter? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to not count hidden cells?
Hi
The Subtotal function does ignore all rows that are hidden by Autofilter, but not rows that are manually hidden. In XL2003 onward, you can get Subtotal to ignore all hidden rows (manual and autofiltered) by placing 100 in from of the Subtotal argument, e.g. =SUBTOTAL(109,A1:A100) =SUBTOTAL(103,A1:A100 -- Regards Roger Govier "Jimbob" wrote in message ... I think I may have worded that wrong. When looking at the subtotal funtion, I find that it still seems to count items that are filtered out using autofilter. Is there a way not to count the items that are filtered out by autofilter? "Wigi" wrote: Hi Have a look at the SUBTOTAL function. In particular, pay attention to which argument you should use. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Jimbob" wrote: I am trying to set up a log, of sorts that will allow me to keep running totals. I need this to take the numbers out as the rows are hidden (upon completion of the task). Is this a possiblity through any normal excel functions, or is this something that will require a macro? I am running excel 2003. Thanks, |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it possible to not count hidden cells?
In XL2003 onward, you can get Subtotal to ignore all hidden rows (manual
and autofiltered) by placing 100 in from of the Subtotal argument, e.g. That should have read In XL2003 onward, you can get Subtotal to ignore all hidden rows (manual and autofiltered) by adding 100 to the Subtotal argument, e.g. 109 instead of 9, 103 instead of 3 -- Regards Roger Govier "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... Hi The Subtotal function does ignore all rows that are hidden by Autofilter, but not rows that are manually hidden. In XL2003 onward, you can get Subtotal to ignore all hidden rows (manual and autofiltered) by placing 100 in from of the Subtotal argument, e.g. =SUBTOTAL(109,A1:A100) =SUBTOTAL(103,A1:A100 -- Regards Roger Govier "Jimbob" wrote in message ... I think I may have worded that wrong. When looking at the subtotal funtion, I find that it still seems to count items that are filtered out using autofilter. Is there a way not to count the items that are filtered out by autofilter? "Wigi" wrote: Hi Have a look at the SUBTOTAL function. In particular, pay attention to which argument you should use. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Jimbob" wrote: I am trying to set up a log, of sorts that will allow me to keep running totals. I need this to take the numbers out as the rows are hidden (upon completion of the task). Is this a possiblity through any normal excel functions, or is this something that will require a macro? I am running excel 2003. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I paste hidden cells and keep them hidden? | Excel Discussion (Misc queries) | |||
Count with Hidden cells | Excel Discussion (Misc queries) | |||
Unique Count sensitive to hidden/filtered rows | Excel Worksheet Functions | |||
Hidden Cells | Excel Discussion (Misc queries) | |||
hidden cells | Excel Discussion (Misc queries) |