 Working With Visible And Hidden Cells  Excel's built in worksheet functions provide almost no support for working with hidden ranges. The closest you can get is the SUBTOTAL function, but this recognizes only cells that are hidden by Excel's Filter tools. It does not support rows or columns that are hidden normally.

However, with a simple VBA function, you can work with hidden cells on a worksheet. The basic VBA function is shown below:

```Public Function IsVisible(InRange As Range) As Boolean()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsVisible
' This function returns an array of Boolean values indicating whether the
' corresponding cell in InRange is visible.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim R As Range
Dim Arr() As Boolean
Dim RNdx As Integer
Dim CNdx As Integer

ReDim Arr(1 To InRange.Rows.Count, 1 To InRange.Columns.Count)
For RNdx = 1 To InRange.Rows.Count
For CNdx = 1 To InRange.Columns.Count
Set R = InRange(RNdx, CNdx)
If R.EntireRow.Hidden = True Or R.EntireColumn.Hidden = True Then
Arr(RNdx, CNdx) = False
Else
Arr(RNdx, CNdx) = True
End If
Next CNdx
Next RNdx
IsVisible = Arr
End Function
```

This function takes as an input parameter the range of cells to test. It returns as its result an array of Boolean (True or False) values indicating whether the corresponding cell in the range specified by InRange is visible (True) or hidden (False). In almost all circumstances, you will use the IsVisible function in an array formula. Because IsVisible returns an array, you can aggregate the result of the function in using other functions such as SUM, or AVERAGE. Using IsVisible In Other Functions

You can use the result of IsVisible in other formulas, typically in array formulas (ArrayFormulas.aspx). The following are examples of using the IsVisible function to peform various operations. All of the formulas below are array formulas so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later.

Count Of Visible Cells
=SUM(--IsVisible(D11:D20))
This returns the number of visible cells in the range D11:D20. The double negation (--) converts the Boolean values returned by IsVisible to their numeric equivalents (True = 1, False = 0).

Sum Of Visible Cells
=SUM(IF(IsVisible(D11:D20),D11:D20,FALSE))
This returns the sum of the values in the visible cells within the range D11:D20. The same result can also be found with the shorter function

=SUM(IsVisible(D11:D20)*D11:D20)

Average Of Visible Cells
=AVERAGE(IF(IsVisible(D11:D20),D11:D20,FALSE))
This returns the average of visible cells in the range D11:D20.