Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Thanks for any help you can give me.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ignoring hidden cells in Calculations (2000)

    Have a look at the SUBTOTAL worksheet function.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Any other ideas!?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •