Ignoring hidden cells in Calculations (2000)

Hi,

I have a simple spreadsheet showing rows of data. Underneath are some formulas with SUM and COUNT functions to calculate Totals and Count.

Now, my problem is this. If I choose to Hide some rows of data, the Totals and Count don't change, as the hidden rows are still included in the calculations. What can I do to change the spreadsheet so the values in the hidden rows will not be included.

Re: Ignoring hidden cells in Calculations (2000)

I'm afraid this doesn't work either. The help files say it ignores hidden rows in a filtered list, but it does not work for the Hide command. I would prefer to hide the rows using the hide command, as filters are not really appropriate for this list.

Re: Ignoring hidden cells in Calculations (2000)

You can create custom functions (add these to a module)

<pre>Option Explicit
Function CountVisible(rng As Range)
Application.Volatile
Dim rCell As Range
CountVisible = 0
For Each rCell In rng
If Not (rCell.Rows.Hidden) And IsNumeric(rCell.Value) Then _
CountVisible = CountVisible + 1
Next
End Function
Function SumVisible(rng As Range)
Application.Volatile
Dim rCell As Range
SumVisible = 0
For Each rCell In rng
If Not (rCell.Rows.Hidden) And IsNumeric(rCell.Value) Then _
SumVisible = SumVisible + rCell.Value
Next
End Function</pre>

Then you can use in a cell (something like):
=SumVisible(A1:A10)
or
=CountVisible(A1:A10)

BEWARE: the functions will only update when you recaclc the worksheet (F9) or edit the cell (F2 - <enter>) the result is in. It will not update when the a row is hidden or unhidden. These (hiding/unhiding rows) do not trigger calculation events, (presumably) since excel does not have built-in methods to calc based on hidden rows, so you must do it manually (via F9).