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,

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,

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,

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?

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,

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,