# Count visible cells filtered data

#### UKNOWINVU2

##### New Member
I am trying to count visible cells only between 1.5 & 2. I used this formula:

=COUNTIF(J7:J83952,">=1.5")-COUNTIF(J7:J83952,">2")

But how do it get this to work with filtered data? Also, is there a way to add another column of data to search (N7:N83952)

Last edited:

#### James006

##### Well-known Member
Hi,

You can use the subtotal function : =Subtotal(103, yourRange) to count visible rows

Hope this will help

#### UKNOWINVU2

##### New Member
Hi,

You can use the subtotal function : =Subtotal(103, yourRange) to count visible rows

Hope this will help
Your idea works to count everything but only want to count the cells with values between 1.5 & 2.

#### James006

##### Well-known Member
Hi again,

In order to add more criteria, would recommend using sumproduct ...

You could test following:

=SUMPRODUCT((J7:J83952">=1.5")*(J7:J83952">2")*SUBTOTAL(3,OFFSET(J7,ROW(J7:J83952)-MIN(ROW(J7:J83952)),0)))

Hope this will help

#### UKNOWINVU2

##### New Member
Hi again,

In order to add more criteria, would recommend using sumproduct ...

You could test following:

=SUMPRODUCT((J7:J83952">=1.5")*(J7:J83952">2")*SUBTOTAL(3,OFFSET(J7,ROW(J7:J83952)-MIN(ROW(J7:J83952)),0)))

Hope this will help
This did not work. Quotes in wrong spots? When quotes removed it did not generate the correct answer.

#### UKNOWINVU2

##### New Member

How can I write a formula to do the follwoing:

If cell B7 contains the word "W2" & J7 is between 1.5 & 2 count 1, plus N7 is between 1.5 & 2

#### Yongle

##### Well-known Member
Your idea works to count everything but only want to count the cells with values between 1.5 & 2.
So :

.... filter ... leaving only rows you want to count visible ... and use the SUBTOTAL function to count those rows

Last edited:

#### James006

##### Well-known Member
Sorry ...copied your criteria without removing the quotes ...

Are you working with Numbers or Text ?

#### UKNOWINVU2

##### New Member
Sorry ...copied your criteria without removing the quotes ...

Are you working with Numbers or Text ?
Numbers. Forget about my second reply stating if A7 contains the word "W2". I am trying to still get my first request.

I am trying to count visible cells only between 1.5 & 2. I used this formula:

=COUNTIF(J7:J83952,">=1.5")-COUNTIF(J7:J83952,">2")

But how do it get this to work with filtered visible data, meaning it dynamical changes when data is filtered. Also, is there a way to add another column of data to search (N7:N83952)

Last edited: