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?