How do I use SUM or SUBTOTAL ignoring the hidden rows.
I have tried using 109 but it only comes up with VALUE
I am using XP Small Business

If my memory is correct, those 100 series numbers were added in xl2003.

=subtotal() will ignore rows hidden by an autofilter, though.

Carol wrote:

How do I use SUM or SUBTOTAL ignoring the hidden rows.
I have tried using 109 but it only comes up with VALUE
I am using XP Small Business

--

Dave Peterson

Thanks but we don't autofilter we hide the row(s) in question need a total.

Anybody else any views please???

Carol

"Dave Peterson" wrote:

If my memory is correct, those 100 series numbers were added in xl2003.

=subtotal() will ignore rows hidden by an autofilter, though.

Carol wrote:

How do I use SUM or SUBTOTAL ignoring the hidden rows.
I have tried using 109 but it only comes up with VALUE
I am using XP Small Business

--

Dave Peterson

You'll need a little user defined function that does the work for you:

Option Explicit
Function SumVisible(rng As Range)

Application.Volatile

Dim myTotal As Double
Dim myCell As Range

myTotal = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If myCell.EntireRow.Hidden = False _
And myCell.EntireColumn.Hidden = False Then
myTotal = myTotal + myCell.Value
End If
End If
Next myCell

SumVisible = myTotal

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumvisible(a1:a100)

Be aware that some versions of excel won't do a calculation when you hide a
row. So your results could be one calculation behind. Force a new recalc
before you trust that answer.



Carol wrote:

Thanks but we don't autofilter we hide the row(s) in question need a total.

Anybody else any views please???

Carol

"Dave Peterson" wrote:

If my memory is correct, those 100 series numbers were added in xl2003.

=subtotal() will ignore rows hidden by an autofilter, though.

Carol wrote:

How do I use SUM or SUBTOTAL ignoring the hidden rows.
I have tried using 109 but it only comes up with VALUE
I am using XP Small Business

--

Dave Peterson


--

Dave Peterson

Thanks for that but is there an easier way as I don't understand how to
'paste the code in there'
What is the code??

Yes I know I'm dim

"Dave Peterson" wrote:

You'll need a little user defined function that does the work for you:

Option Explicit
Function SumVisible(rng As Range)

Application.Volatile

Dim myTotal As Double
Dim myCell As Range

myTotal = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If myCell.EntireRow.Hidden = False _
And myCell.EntireColumn.Hidden = False Then
myTotal = myTotal + myCell.Value
End If
End If
Next myCell

SumVisible = myTotal

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumvisible(a1:a100)

Be aware that some versions of excel won't do a calculation when you hide a
row. So your results could be one calculation behind. Force a new recalc
before you trust that answer.



Carol wrote:

Thanks but we don't autofilter we hide the row(s) in question need a total.

Anybody else any views please???

Carol

"Dave Peterson" wrote:

If my memory is correct, those 100 series numbers were added in xl2003.

=subtotal() will ignore rows hidden by an autofilter, though.

Carol wrote:

How do I use SUM or SUBTOTAL ignoring the hidden rows.
I have tried using 109 but it only comes up with VALUE
I am using XP Small Business

--

Dave Peterson


--

Dave Peterson

The code is everything between (and including)

Option Explicit
....
End Sub

And if you read that link or follow those instructions, you may find it not so
difficult.

The only other way I know is to upgrade to xl2003.

Carol wrote:

Thanks for that but is there an easier way as I don't understand how to
'paste the code in there'
What is the code??

Yes I know I'm dim

"Dave Peterson" wrote:

You'll need a little user defined function that does the work for you:

Option Explicit
Function SumVisible(rng As Range)

Application.Volatile

Dim myTotal As Double
Dim myCell As Range

myTotal = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If myCell.EntireRow.Hidden = False _
And myCell.EntireColumn.Hidden = False Then
myTotal = myTotal + myCell.Value
End If
End If
Next myCell

SumVisible = myTotal

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumvisible(a1:a100)

Be aware that some versions of excel won't do a calculation when you hide a
row. So your results could be one calculation behind. Force a new recalc
before you trust that answer.



Carol wrote:

Thanks but we don't autofilter we hide the row(s) in question need a total.

Anybody else any views please???

Carol

"Dave Peterson" wrote:

If my memory is correct, those 100 series numbers were added in xl2003.

=subtotal() will ignore rows hidden by an autofilter, though.

Carol wrote:

How do I use SUM or SUBTOTAL ignoring the hidden rows.
I have tried using 109 but it only comes up with VALUE
I am using XP Small Business

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson