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.
.