Is it possible to not count hidden cells?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
Hi

Have a look at the SUBTOTAL function.

In particular, pay attention to which argument you should use.
 
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?
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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
 
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.
Click to expand...

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
 
Back
Top