summing the visible

[wwbwb]

I've searched for it, but perhaps I've missed it. I have data in column e starting at e5:e46. Most of the rows are hidden because they are conditionally set that way because there is no data in one of the other columns. In e47 I have a sum of e5:e46 and it works just fine. What I am looking for is a formula that when put in e47 will sum ONLY the visible data. So if only 5 rows are showing, it only sums those 5 rows, if I unhide the rows it will sum all the rows or however many are showing.

 

[briankelly]

If you can use a filter instead of hiding the rows there is a solution. Use =subtotal(9,E5:E46) - this will be dynamic as your filter changes and only show the total of the visible cells.

 

[wwbwb]

thanks.

 

[just_jon]

I've searched for it, but perhaps I've missed it. I have data in column e starting at e5:e46. Most of the rows are hidden because they are conditionally set that way because there is no data in one of the other columns. In e47 I have a sum of e5:e46 and it works just fine. What I am looking for is a formula that when put in e47 will sum ONLY the visible data. So if only 5 rows are showing, it only sums those 5 rows, if I unhide the rows it will sum all the rows or however many are showing.

If visible/not visible is set by a certain column - say, A - being blank, couldn't you usr --

=SUMIF(A5:A46,"<>",E5:E46)

 

[Tom Urtis]

It sounds like your rows are hidden because you hid them manually, not because of a filter. The subtotal formula does not work with rows hidden due to a filter. There are add-ins for this but for a VBA solution using a User Defined Function, you can also achieve your goal by placing the following UDF code in a standard module:

Function SumViz(RangeToSum As Range)
Dim Cell As Range
Application.Volatile
SumViz = 0
For Each Cell In RangeToSum
If Cell.Rows.Hidden = False And Cell.Columns.Hidden = False Then
If IsNumeric(Cell.Value) Then
SumViz = SumViz + Cell.Value
End If
End If
Next
End Function


To use the code as a formula, enter the following formula in cell E47 or wherever you want, outside of E5:E46 - -

=SumViz(E5:E46)

This will also serve you if you end up using a filter. The UDF works for both scenarios; the Subtotal only works for filtered.

 

[Zack Barresse]

=SUMIF(D5:D46, "<>" & "",E5:E46)

where column D houses your criteria.

HTH