Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
=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. -- Thank you! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
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) "Doug" wrote in message ... =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. -- Thank you! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
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:A 10)) 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] On Mon, 21 Dec 2009 15:14:01 -0800, Doug wrote: =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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
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] On Mon, 21 Dec 2009 18:50:37 -0500, "JLGWhiz" wrote: 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) "Doug" wrote in message ... =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. -- Thank you! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
Yep, Chip, I overlooked that. I started to write some code for it but see
that you already did. "Chip Pearson" wrote in message ... 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] On Mon, 21 Dec 2009 18:50:37 -0500, "JLGWhiz" wrote: 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) "Doug" wrote in message ... =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. -- Thank you! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
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 -- Data Hog "Doug" wrote: =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. -- Thank you! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
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)*ISNUM BER(CF3:CF1000)) -- Thank you! "Chip Pearson" wrote: 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:A 10)) 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] On Mon, 21 Dec 2009 15:14:01 -0800, Doug wrote: =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. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
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:A61000 )*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. 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] On Mon, 21 Dec 2009 21:44:01 -0800, Doug wrote: 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)*ISNU MBER(CF3:CF1000)) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
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" wrote: 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:A61000 )*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. 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] On Mon, 21 Dec 2009 21:44:01 -0800, Doug wrote: 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)*ISNU MBER(CF3:CF1000)) . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countif
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.AutoFi lter Field:=24, Criteria1 _ :="=-500", Operator:=xlAnd, Criteria2:="<=500" Application.Goto [A3], True End Sub -- Thank you! "Chip Pearson" wrote: 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:A61000 )*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. 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] On Mon, 21 Dec 2009 21:44:01 -0800, Doug wrote: 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)*ISNU MBER(CF3:CF1000)) . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Countif | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |