Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    34

    Unanswered: Sum if row height > 0

    Can i sum cells in one column, the condition is ( the row height is greater than zero, i.e the row is not hidden), without using vb code.

    thank you.

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    i dont think you can unless your hidden rows are by the way of a filter then you can use the SubTotal Function,

    or else here is a udf to do what you want but be careful with the use it doesn't update when you hide a row so youll have to update the formula manually yourself

    Code:
    Function SUMVISIBLE(ByVal rng As Range) As Double
        Dim Cell As Range
        'check to see if cell is vissible and add to total if it is
        For Each Cell In rng
            If Cell.EntireRow.Hidden = False Or Cell.EntireColumn.Hidden Then
                SUMVISIBLE = SUMVISIBLE + Cell.Value
            End If
        Next Cell
            
    End Function
    Dave

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by ridwan
    Can i sum cells in one column, the condition is ( the row height is greater than zero, i.e the row is not hidden), without using vb code.
    Check out the 'SubTotals' Function. The Row height will not have any significance, however if you use Filter or AutoFilter on a list or table and use the function 'SubTotals' instead of 'Sum' it will show only the visible rows, those rows meeting the filter criteria.

    A trick to setting the SubTotal function on a column is to first filter a list, then select a cell below the list where you want the total and click the 'Auto Sum' toolbar button (the one that looks like a funky E) If the column is filtered it will apply a subtotal formula. Without a filtered selection you will get a regular 'Sum' function. This formula is the subtoal for column A.

    =SUBTOTAL(9,A1:A16)

    If the filter is changed to show different rows in the filter the total will change to reflect the visible rows. Comes in handy, i prefer this to using sum. Look at the Excel Help for subtotal for additional parameters.


    .
    ~

    Bill

Posting Permissions

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