# Thread: Sum if row height > 0

1. Registered User
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. Registered User
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. Registered User
Join Date
Feb 2004
Posts
533
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.

.

#### Posting Permissions

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