Count visible cells filtered data

[UKNOWINVU2]

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)

 

[James006]

Hi,

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

Hope this will help

 

[UKNOWINVU2]

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]

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]

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]

How about this?

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]

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

 

[James006]

Sorry ...copied your criteria without removing the quotes ...

Are you working with Numbers or Text ?

 

[UKNOWINVU2]

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)