count only visible cells

B

bnkone

I am using an autofilter on a set of data. I would like to use a countif
field to summarize the visible cells only. So, every time I change the
autofilter I would change the criteria counted.

Any ideas out there?
 
P

Peo Sjoblom

Look at the subtotal function in help, it works on visible cells only

=SUBTOTAL(9,A2:A1000)

will sum A2:A1000 and when filtered it will sum only the visible cells in
A2:A1000

=SUBTOTAL(3,A2:A1000)

will count non empty visible cells


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
B

bnkone

Thanks,

Subtotal(3,) did the job.


Peo Sjoblom said:
Look at the subtotal function in help, it works on visible cells only

=SUBTOTAL(9,A2:A1000)

will sum A2:A1000 and when filtered it will sum only the visible cells in
A2:A1000

=SUBTOTAL(3,A2:A1000)

will count non empty visible cells


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
Click to expand...
 
Top