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:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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
5,617
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:

Watch MrExcel Video

Forum statistics

Threads
1,095,173
Messages
5,442,828
Members
405,199
Latest member
mkarnout

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top