# 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:

### 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
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:

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...