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

UKNOWINVU2

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

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,764
Office Version
365
Platform
Windows
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

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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,764
Office Version
365
Platform
Windows
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,764
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,095,751
Messages
5,446,284
Members
405,394
Latest member
WStockel

This Week's Hot Topics

Top