LinkBack URL
About LinkBacksGot it! Thanks so much, Gary.
Mark
"Gary L Brown" wrote:
> Good news/ Bad News...
> Good news...
> - You definitely have the SubTotal() function because otherwise Excel would
> be saying #NAME? instead of #VALUE!
> - You simply need to change the formula from a 9 to a 3. ie:
> =SubTotal(3,A1:A10) vs =SubTotal(9,A1:A10)
>
> Bad news...
> - There are one or more cells in the range you selected to sum/count that
> have an error value of #VALUE!
> - You are going to have to find and fix them prior to this built-in function
> working as you want it to.
>
> FYI,
> In HELP you will find that..
> 9 = sum
> 3 = counta
> 1 = average
> 2 = count
> 4 = max
> etc thru 11 = varp
>
> HTH,
> --
> Gary Brown
> gary_brown@ge_NOSPAM.com
> If this post was helpful, please click the ''''Yes'''' button next to
> ''''Was this Post Helpfull to you?".
>
>
> "Mark" wrote:
>
> > Subtotal was not an option so I typed it in. Result provided is #VALUE!.
> > Did I tell you this column is a list of names, not numbers? I am not summing
> > up numbers. I just want to know how many rows there are because some are
> > hidden and final row, called 2700 by Excel, is too large. I hope I am clear
> > about this.
> >
> > "Gary L Brown" wrote:
> >
> > > Let's make some assumptions for this example.
> > > You have Autofiltered information in Cells A2 thru A10.
> > > You want your Sum to be in Cell A11
> > >
> > > - Go to cell A11
> > > - From the top menu, select Insert>Function
> > > - In the 'Function Name:' window, scroll down to 'SUBTOTAL'
> > > - Highlight 'SUBTOTAL'
> > > - Select OK
> > > - In the function window that appears..
> > > - In the first window 'Function_num', type 9
> > > - Go to the Ref1 box so that you can select a range
> > > - You may have to move the function window if it is in the way of the
> > > range you want to select
> > > - Hightlight the cells that you want to Sum
> > > - Select OK
> > >
> > > HTH,
> > > --
> > > Gary Brown
> > > gary_brown@ge_NOSPAM.com
> > > If this post was helpful, please click the ''''Yes'''' button next to
> > > ''''Was this Post Helpfull to you?".
> > >
> > >
> > > "Mark" wrote:
> > >
> > > > I'm afraid I am a rank amateur at this. I have Office 2002 and skipped rows
> > > > are indeed due to use of Auto Filter, Advanced Filter, along with some
> > > > manually deleted rows. Sounds like your Subtotal function might do it. But
> > > > how exactly to I perform this? When I attempt to highlight rows in far upper
> > > > left, entire spreadsheet is highlighted. Also in function dialogue box, I
> > > > don't see Subtotal function.
> > > >
> > > > "Gary L Brown" wrote:
> > > >
> > > > > Hi Mark,
> > > > > There are 3 possible answers but it depends on how you are hiding the rows.
> > > > >
> > > > > 1) If you are using Autofilter then the SubTotal() function will work.
> > > > > =SubTotal(9,A1:A10) will sum only visible rows in the range A1 to A10
> > > > > =SubTotal(3,A1:A10) will count only visible rows in the range A1 to A10
> > > > >
> > > > > 2) If you are MANUALLY hidding the rows AND you have Excel 2003, you can use
> > > > > the new feature of the SubTotal() function...
> > > > > =SubTotal(109,A1:A10) will sum only visible rows in the range A1 to A10
> > > > >
> > > > > 3) If you are MANUALLY hidding the rows AND you DO NOT have Excel 2003, you
> > > > > need a macro to do this such as the one below.
> > > > >
> > > > > '/=============================================/
> > > > > Public Function Sum_Visible_Range(rng As Range) As Variant
> > > > > 'sum numbers in visible rows and columns only
> > > > > Dim rngCell As Range
> > > > > Dim varSum As Variant
> > > > >
> > > > > Application.Volatile
> > > > >
> > > > > varSum = 0
> > > > >
> > > > > For Each rngCell In rng
> > > > > If IsNumeric(rngCell.Value) = True Or _
> > > > > IsDate(rngCell.Value) Then
> > > > > If rngCell.EntireRow.Hidden = False And _
> > > > > rngCell.EntireColumn.Hidden = False Then
> > > > > varSum = varSum + rngCell.Value
> > > > > End If
> > > > > End If
> > > > > Next rngCell
> > > > >
> > > > > Sum_Visible_Range = varSum
> > > > >
> > > > > End Function
> > > > > '/=============================================/
> > > > >
> > > > > HTH,
> > > > > --
> > > > > Gary Brown
> > > > > gary_brown@ge_NOSPAM.com
> > > > > If this post was helpful, please click the ''''Yes'''' button next to
> > > > > ''''Was this Post Helpfull to you?".
> > > > >
> > > > >
> > > > > "Mark" wrote:
> > > > >
> > > > > > All I really want is the sum of all rows, since the number of the last row
> > > > > > reflects skipped rows. For example, last row says 2400, but it's actually
> > > > > > less since there are hidden rows. I want the actually number of rows
> > > > > > showing. Is there a simple way to get this?
> > > > > >
> > > > > > "Gary L Brown" wrote:
> > > > > >
> > > > > > > Try this macro - select the range you want to put the numbers first and put
> > > > > > > your starting number in the first cell of the selection.
> > > > > > >
> > > > > > > '/=================================================/
> > > > > > > Sub Row_List()
> > > > > > > Dim rngCell As Range
> > > > > > > Dim strAddress As String
> > > > > > >
> > > > > > > strAddress = Selection.Range("A1").Address
> > > > > > >
> > > > > > > For Each rngCell In Selection
> > > > > > > If Hidden_Row(rngCell) = False Then
> > > > > > > If strAddress <> rngCell.Address Then
> > > > > > > rngCell.Formula = "=" & strAddress & " + 1"
> > > > > > > strAddress = rngCell.Address
> > > > > > > End If
> > > > > > > End If
> > > > > > > Next rngCell
> > > > > > >
> > > > > > > End Sub
> > > > > > > '/=================================================/
> > > > > > > Public Function Hidden_Row(rng As Range) As Long
> > > > > > > 'return 1 if row is hidden, 0 if row is visible
> > > > > > > Application.Volatile
> > > > > > >
> > > > > > > On Error Resume Next
> > > > > > > Hidden_Row = 0
> > > > > > >
> > > > > > > If rng.EntireRow.Hidden = True Then
> > > > > > > Hidden_Row = 1
> > > > > > > End If
> > > > > > >
> > > > > > > End Function
> > > > > > > '/=================================================/
> > > > > > >
> > > > > > > HTH,
> > > > > > > --
> > > > > > > Gary Brown
> > > > > > > gary_brown@ge_NOSPAM.com
> > > > > > > If this post was helpful, please click the ''''Yes'''' button next to
> > > > > > > ''''Was this Post Helpfull to you?".
> > > > > > >
> > > > > > >
> > > > > > > "Mark" wrote:
> > > > > > >
> > > > > > > > I have done a number of sorts and filters and rows now skip the numbers that
> > > > > > > > are hidden. Now I am satisfied with the rows showing and I want a straight
> > > > > > > > numerical sequence of the rows shown, without any numbers skipped. Can I get
> > > > > > > > this?
Register To Reply
Bookmarks