Visible cell count CHALLENGE.. wait for it, BETWEEN VALUES!!

[UKNOWINVU2]

How do I count visible (filtered) cells in 2 columns (data range below) with values between 1.5 & 2. Meaning if cell value is equal to or between 1.5 & 2 it counts.

I have figured out how to do this with unfiltered data but cant seem to figure it out with filtered data, HELP PLEASE!!!!!

Data: J7:J100000 & N7:N100000

 

[Fluff]

Have a look here https://exceljet.net/formula/count-visible-rows-only-with-criteria

 

[UKNOWINVU2]

Have a look here https://exceljet.net/formula/count-visible-rows-only-with-criteria

Hi Fluff, ill be honest naturally I googled this question and came across that site however I dont understand how to manipulate the formula to work for my scenario.

 

[Fluff]

You neeed to change it along the lines of
=SUMPRODUCT((J7:J14>=1.5)*(J7:J14<=2)*(SUBTOTAL(103,OFFSET(J7,ROW(J7:J14)-MIN(ROW(J7:J14)),0))))

 

[UKNOWINVU2]

OMG thats crazy, it works!! Thank you so much!!! Ok 2 more questions...please...

1) Why the solo J7 entry shortly after 103, what does that mean.. the J7?
2) Is it possible to modify the formula to include an only count if the corresponding row cell A7 contains the "text" "*W3*"?

Or is this asking to much?

 

[UKNOWINVU2]

OMG thats crazy, it works!! Thank you so much!!! Ok 2 more questions...please...

1) Why the solo J7 entry shortly after 103, what does that mean.. the J7?
2) Is it possible to modify the formula to include an only count if the corresponding row cell A7 contains the "text" "*W3*"?

Or is this asking to much?

Or better yet, for question # 2, I dont need to filter count if i can say:

If A7 has text "W3" & Cell J7 is equal to or between 1.5 & 2 count. I feel like that might be easier, no?

 

[Fluff]

The J7 is just the start point for the OFFSET function.
for the extra criteria, try
=SUMPRODUCT((J7:J14>=1.5)*(J7:J14<=2)*(ISNUMBER(SEARCH("W3",A7:A14))))*(SUBTOTAL(103,OFFSET(J7,ROW(J7:J14)-MIN(ROW(J7:J14)),0)))

 

[UKNOWINVU2]

The J7 is just the start point for the OFFSET function.
for the extra criteria, try
=SUMPRODUCT((J7:J14>=1.5)*(J7:J14<=2)*(ISNUMBER(SEARCH("W3",A7:A14))))*(SUBTOTAL(103,OFFSET(J7,ROW(J7:J14)-MIN(ROW(J7:J14)),0)))

*round of applause* BRILLIANT!!! Thank you Fluff!

 

[Fluff]

You're welcome & thanks for the feedback