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.

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

Dave

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.


.