Countif

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

=COUNTIF($CF3:$CF1000,"<2000")

How can I get this to return the total count excluding the hidden cells in
the range?
Right now it shows all.
 
Excel does not provide that function, but John Walkenbach does. Put this in
your public code module:

Function COUNTVISIBLE(rng)
' Counts visible cells
Dim CellCount As Long
Dim cell As Range
Application.Volatile
CellCount = 0
Set rng = Intersect(rng.Parent.UsedRange, rng)
For Each cell In rng
If Not IsEmpty(cell) Then
If Not cell.EntireRow.Hidden And _
Not cell.EntireColumn.Hidden Then _
CellCount = CellCount + 1
End If
Next cell
COUNTVISIBLE = CellCount
End Function


If you want only visible cells for C10:C250 then

=COUNTVISIBLE(C10:C250)
 
You could do it with a simple VBA function. Use the following code:

Function IsVisible(RR As Range) As Boolean()
Dim Arr() As Boolean
Dim RNdx As Long
Dim CNdx As Long
ReDim Arr(1 To RR.Rows.Count, 1 To RR.Columns.Count)
For RNdx = 1 To RR.Rows.Count
For CNdx = 1 To RR.Columns.Count
If RR(RNdx, CNdx).EntireRow.Hidden = False And _
RR(RNdx, CNdx).EntireColumn.Hidden = False Then
Arr(RNdx, CNdx) = True
Else
Arr(RNdx, CNdx) = False
End If
Next CNdx
Next RNdx
IsVisible = Arr
End Function


This returns an array of Booleans, True indicating the cell in RR is
visible, False indicating the cell in RR is hidden.

Then, use this result in an array formula like

=SUM(IsVisible(A1:A10)*(A1:A10<1000)*ISNUMBER(A1:A10))

This returns the count of values in A1:A10 that are numeric, visible,
and less than 1000.

This is an array formula, so you must press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever
you edit it later. If you do this properly, Excel will display
the formula enclosed in curly braces { }. You do not type
in the braces -- Excel puts them in automatically. The
formula will not work properly if you do not enter it with
CTRL SHIFT ENTER. For much more information about array
formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
That doesn't address the CountIF part of the question.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
Yep, Chip, I overlooked that. I started to write some code for it but see
that you already did.


Chip Pearson said:
That doesn't address the CountIF part of the question.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
Click to expand...
 
Since you have some hidden rows, you might have auto filter turned on.

If you use auto filter and query on <2000 in Column CF
place this formula in cell CF1002:

=subtotal(102,CF3:CF1000)

the 102 counts and ignores hidden values

HTH
 
I am excited about the functionality of this. I can probably use it on other
applications. Could you please check and make sure this is right. I entered
it as you said and the curly braces appeared, but it says #N/A in the cell.
I placed the function in a separate module where I assume it should go?

=SUM(IsVisible(CF3:CF1000)*(CF3:CF1000<2000)*ISNUMBER(CF3:CF1000))
--
Thank you!


Chip Pearson said:
You could do it with a simple VBA function. Use the following code:

Function IsVisible(RR As Range) As Boolean()
Dim Arr() As Boolean
Dim RNdx As Long
Dim CNdx As Long
ReDim Arr(1 To RR.Rows.Count, 1 To RR.Columns.Count)
For RNdx = 1 To RR.Rows.Count
For CNdx = 1 To RR.Columns.Count
If RR(RNdx, CNdx).EntireRow.Hidden = False And _
RR(RNdx, CNdx).EntireColumn.Hidden = False Then
Arr(RNdx, CNdx) = True
Else
Arr(RNdx, CNdx) = False
End If
Next CNdx
Next RNdx
IsVisible = Arr
End Function


This returns an array of Booleans, True indicating the cell in RR is
visible, False indicating the cell in RR is hidden.

Then, use this result in an array formula like

=SUM(IsVisible(A1:A10)*(A1:A10<1000)*ISNUMBER(A1:A10))

This returns the count of values in A1:A10 that are numeric, visible,
and less than 1000.

This is an array formula, so you must press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever
you edit it later. If you do this properly, Excel will display
the formula enclosed in curly braces { }. You do not type
in the braces -- Excel puts them in automatically. The
formula will not work properly if you do not enter it with
CTRL SHIFT ENTER. For much more information about array
formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





=COUNTIF($CF3:$CF1000,"<2000")

How can I get this to return the total count excluding the hidden cells in
the range?
Right now it shows all.
Click to expand...
.
Click to expand...
 
Your formula appears to be correct -- I don't see anything wrong with
it. The first thing that comes to mind is that you have an #N/A error
in your input data in CF3:CF1000. This error will cause an #N/A in the
formula. Use the following revised array formula to exclude #N/A
errors from the calculation:

=SUM(IF(ISNA(A1:A6),0,IsVisible(A1:A6)*(A1:A6>1000)*ISNUMBER(A1:A6)))

If this is not the problem, then you need to debug the formula and the
VBA code. The first way to test to find the cause of the #N/A is to
use the Formula Evaluate tool on the Auditing command bar to evaluate
the formula one step at a time. If the #N/A is coming out of the VBA
function, go to VBA, select the ReDim statement and press F9 to set a
breakpoint on that line. Then, back in Excel, press F2 and then CTRL
SHIFT ENTER to force the formula to recalculate. Code execution will
pause on the breakpoint, and you can then use F8 to step through the
code line by line. If the code just terminates on a line other than
at the normal exit point at the end of the procedure, that line is
causing a problem.

I've tried various combinations of data and visible/hidden cells and
cannot get the function to return #N/A unless there is an #N/A in the
input data.
I can probably use it on other applications.
Click to expand...

That's why I write functions to be general and generic -- once
written, they can be used in any number of situations. After a while,
you accumulate a large library of very useful functions. This
particular functions is described on my web site at
http://www.cpearson.com/Excel/IsVisible.aspx.


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
It works great accept when I refilter data in the table. Then it says #VALUE!
and I have to reset it for each cell I have the formula entered into. Is
there a way around this?
--
Thank you!


Chip Pearson said:
Your formula appears to be correct -- I don't see anything wrong with
it. The first thing that comes to mind is that you have an #N/A error
in your input data in CF3:CF1000. This error will cause an #N/A in the
formula. Use the following revised array formula to exclude #N/A
errors from the calculation:

=SUM(IF(ISNA(A1:A6),0,IsVisible(A1:A6)*(A1:A6>1000)*ISNUMBER(A1:A6)))

If this is not the problem, then you need to debug the formula and the
VBA code. The first way to test to find the cause of the #N/A is to
use the Formula Evaluate tool on the Auditing command bar to evaluate
the formula one step at a time. If the #N/A is coming out of the VBA
function, go to VBA, select the ReDim statement and press F9 to set a
breakpoint on that line. Then, back in Excel, press F2 and then CTRL
SHIFT ENTER to force the formula to recalculate. Code execution will
pause on the breakpoint, and you can then use F8 to step through the
code line by line. If the code just terminates on a line other than
at the normal exit point at the end of the procedure, that line is
causing a problem.

I've tried various combinations of data and visible/hidden cells and
cannot get the function to return #N/A unless there is an #N/A in the
input data.
I can probably use it on other applications.
Click to expand...

That's why I write functions to be general and generic -- once
written, they can be used in any number of situations. After a while,
you accumulate a large library of very useful functions. This
particular functions is described on my web site at
http://www.cpearson.com/Excel/IsVisible.aspx.


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]







I am excited about the functionality of this. I can probably use it on other
applications. Could you please check and make sure this is right. I entered
it as you said and the curly braces appeared, but it says #N/A in the cell.
I placed the function in a separate module where I assume it should go?

=SUM(IsVisible(CF3:CF1000)*(CF3:CF1000<2000)*ISNUMBER(CF3:CF1000))
Click to expand...
.
Click to expand...
 
Sub Clear()
'
I just realized that it only has this problem when I run a macro. Here is a
short macro. Is there a reason that this would turn the isvisible formula
cells to #value! ?

' Clear Macro
' Clears filters and omits all #N/A Fields

Sheets("Screener").Select
Range("Table13423[[#Headers],[Ticker]]").Select
ActiveSheet.ShowAllData
ActiveSheet.ListObjects("Table13423").Range.AutoFilter Field:=24,
Criteria1 _
:=">=-500", Operator:=xlAnd, Criteria2:="<=500"
Application.Goto [A3], True
End Sub
--
Thank you!


Chip Pearson said:
Your formula appears to be correct -- I don't see anything wrong with
it. The first thing that comes to mind is that you have an #N/A error
in your input data in CF3:CF1000. This error will cause an #N/A in the
formula. Use the following revised array formula to exclude #N/A
errors from the calculation:

=SUM(IF(ISNA(A1:A6),0,IsVisible(A1:A6)*(A1:A6>1000)*ISNUMBER(A1:A6)))

If this is not the problem, then you need to debug the formula and the
VBA code. The first way to test to find the cause of the #N/A is to
use the Formula Evaluate tool on the Auditing command bar to evaluate
the formula one step at a time. If the #N/A is coming out of the VBA
function, go to VBA, select the ReDim statement and press F9 to set a
breakpoint on that line. Then, back in Excel, press F2 and then CTRL
SHIFT ENTER to force the formula to recalculate. Code execution will
pause on the breakpoint, and you can then use F8 to step through the
code line by line. If the code just terminates on a line other than
at the normal exit point at the end of the procedure, that line is
causing a problem.

I've tried various combinations of data and visible/hidden cells and
cannot get the function to return #N/A unless there is an #N/A in the
input data.
I can probably use it on other applications.
Click to expand...

That's why I write functions to be general and generic -- once
written, they can be used in any number of situations. After a while,
you accumulate a large library of very useful functions. This
particular functions is described on my web site at
http://www.cpearson.com/Excel/IsVisible.aspx.


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]







I am excited about the functionality of this. I can probably use it on other
applications. Could you please check and make sure this is right. I entered
it as you said and the curly braces appeared, but it says #N/A in the cell.
I placed the function in a separate module where I assume it should go?

=SUM(IsVisible(CF3:CF1000)*(CF3:CF1000<2000)*ISNUMBER(CF3:CF1000))
Click to expand...
.
Click to expand...
 
Back
Top