counting number of visible cells

[khushboo#]

Hi All,

Needed help in counting number of visible cells in filtered data.
I have a sheet1 where i need to manually count visible cells in filtered data and note that count in sheet2.
In attachment you can see 2 sheet so filter will be as mentioned below.
Conditions:

1) sheet 1: range D, Filter will be "0" - Sheet 2: CEll B6 should have that count.
2) sheet 1: range E, Filter will be "only string value except 0 and 1" - Sheet 2: CEll B7 should have that count.
3)sheet 1: range F, Filter will be to "cells does not contain 0 and 1" - Sheet 2: CEll B8 should have that count.

[PCI]

How can you make the difference between each result (B6,B7,B8) .... as the number of visible cells change.
Why not to use a formula to count the number of cells what ever they are visible or not ???
e.g
B6 =COUNTIF(Sheet1!D:D,"=0")
B7=SUMPRODUCT((E2:E1000 <>0)*(E2:E1000 <>1)*(E2:E1000 <>""))

[Marc L]

Hi !

Easy way to count number of visible cells : use the SUBTOTAL worksheet function !

[khushboo#]

Hello PCI,
Thank you for your reply.
I wanted macro only to put these condition filter nad then calculate the number of visible cell.

[PCI]

I wanted macro only to put these condition filter

Do you agree that mean 3 macros for each selection ...?
Is the formulas results correct ?