COUNTIF (or something similar) for visible cells only after filtering. Please please help

Hey guys,

I have had this problem for a few weeks now but I dont know how to solve it. I have a workbook with some large data sets (500,000 rows x 12 columns) and I am applying some conditional filtering to some of the cells. In each column I have some data that excel checks to see whether or not they fall into a certain range. I check to see if each cell is less a defined small value or greater than a defined greater value. If they are out of range, the cells come up highlighted as red.

I use COUNTIF to see how many cells are out of range. However, when I filter some cells (based on index for example) the COUNTIF stays the same because it always takes into account hidden cells.

I would attach my spreadsheet here but its roughly 50mb. I have attached a VI for what I want it to do below (note: This code doesnt work and I just wrote the values manually. This sheet wont work, I just used placeholder values to show a BEFORE for what my sheet currently does, and an AFTER to demonstrate what I want)

Could I please get some help? How do i ensure the CONUTIF doesnt counts my hidden cells that I use for a particular range? (i.e for each cell that is less than X or greater than Y, highlighted as red, when filtered, how many are there?)

Thanks


EDIT: I have attached a file three posts down - Please use that! Thank you!

CF formula refers to C5 and B5 cells which are blank. Limits for upward is given in C3 and B3.
In column J few rows are included and some are left over for CF. What is the criteria for inclusion of cells.

1:

I use COUNTIF to see how many cells are out of range. However, when I filter some cells ... the COUNTIF stays the same because it always takes into account hidden cells.

2:

How do i ensure the COUNTIF counts my hidden cells that I use for a particular range?

So, what do you want: Include or exclude hidden cells?

3: What are the criteria/limits for "SIDEWAYS"?

SUBTOTAL(102: Count visible...
i.e.:

Please Login or Register  to view this content.

&:

Please Login or Register  to view this content.

---

Clearmax=TranslatingTorus!$N$4
ClearMin=TranslatingTorus!$M$4
Criteria=TranslatingTorus!$E$3:$N$4
CurveMax=TranslatingTorus!$H$4
CurveMin=TranslatingTorus!$G$4
LUffMax=TranslatingTorus!$C$4
LuffMin=TranslatingTorus!$B$4
ScopeMax=TranslatingTorus!$C$5
ScopeMin=TranslatingTorus!$B$5
SlewMax=TranslatingTorus!$C$3
SlewMin=TranslatingTorus!$B$3
SumpMax=TranslatingTorus!$F$4
SumpMin=TranslatingTorus!$E$4
UJ1Max=TranslatingTorus!$C$6
UJ1Min=TranslatingTorus!$B$6
UJ2Max=TranslatingTorus!$C$7
UJ2Min=TranslatingTorus!$B$7
XMax=TranslatingTorus!$J$4
XMin=TranslatingTorus!$I$4
Zmax=TranslatingTorus!$L$4
ZMin=TranslatingTorus!$K$4