summing the visible

wwbwb

Well-known Member
Joined
Oct 20, 2003
Messages
513
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

briankelly

New Member
Joined
Aug 7, 2002
Messages
43
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.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
wwbwb said:
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
=SUMIF(D5:D46, "<>" & "",E5:E46)

where column D houses your criteria.

HTH
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,091
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top