Count visible cells filtered data

UKNOWINVU2

New Member
Joined
Jun 26, 2014
Messages
32
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
Joined
Apr 4, 2009
Messages
3,680
Hi,

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

Hope this will help
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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
Joined
Jun 26, 2014
Messages
32
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
Joined
Jun 26, 2014
Messages
32
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,879
Office Version
365
Platform
Windows
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
Joined
Apr 4, 2009
Messages
3,680
Sorry ...copied your criteria without removing the quotes ...

Are you working with Numbers or Text ?
 

UKNOWINVU2

New Member
Joined
Jun 26, 2014
Messages
32
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:

Forum statistics

Threads
1,084,935
Messages
5,380,661
Members
401,695
Latest member
dwoychowski

Some videos you may like

This Week's Hot Topics

Top