Ignore Hidden Rows on Sum Function on Excel XP
 

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

Ignore Hidden Rows on Sum Function on Excel XP
 

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

Ignore Hidden Rows on Sum Function on Excel XP
 

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

Ignore Hidden Rows on Sum Function on Excel XP
 

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

Ignore Hidden Rows on Sum Function on Excel XP
 

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

Ignore Hidden Rows on Sum Function on Excel XP
 

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

Ignore Hidden Rows on Sum Function on Excel XP
 

Thanks for your help I'll have a try

Carol

"Dave Peterson" wrote:

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

Ignore Hidden Rows on Sum Function on Excel XP
 

Hi

I've tried exactly as you have said but now i get an answer of £0.00 when I
adda column of figures what am I doing wrong now?

(Yes I am adding the correct colum using the correct cell references)

Thanks

"Carol" wrote:

Thanks for your help I'll have a try

Carol

"Dave Peterson" wrote:

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

Ignore Hidden Rows on Sum Function on Excel XP
 

Hi Me Again

I've got the spreadsheet to accept SUMVISIBLE and to show a total now but
it's not correct it adds in the hidden rows as well

What am I doing wrong?

Thanks

"Dave Peterson" wrote:

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

Ignore Hidden Rows on Sum Function on Excel XP
 

If the formula returns a number, then I think you did everything ok.

But if you've hidden/unhidden any rows/columns, then formula may not have
recalculated.

Try hitting F9 to see if that helps.



Carol wrote:

Hi Me Again

I've got the spreadsheet to accept SUMVISIBLE and to show a total now but
it's not correct it adds in the hidden rows as well

What am I doing wrong?

Thanks

"Dave Peterson" wrote:

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


--

Dave Peterson

The problem with UDFs is that unless one of its parameters changes, it will not recalculate. By parameters changing, we mean values; not the format properties of the cells containing the values.

Therefore, a change of any format (including row height; a row height of 0 indicates 'hidden') will not recalculate.

Even pressing F9 won't change it. One way around this is to add a dummy date parameter to the function, and simply pass NOW() to it. As time is constantly changing, the formula will always be earmarked by Excel as 'requiring recalculation' each time F9 is pressed or other formulae are recalculated. The formula itself doesn't actually need to do anything with that parameter, but as long as it's declared, it will recalculate.

Notwithstanding all the above, I would say the best thing to do is use a routine which passes back a RANGE object containing only the visible cells. That way, you can do anything you want with those cells - not just SUM. The problem with the above example is that you would have to create a similar function for every possible type of operation you might want - multiplying, averaging, vlookup, etc.

The following routine however allows you to specify a range as VISRANGE(address) and pass it to any built-in (or user-defined) function which accepts a range parameter.

Public Function VisRange(xRange As Range) As Range
Dim xBuildRange As Range

For Each xCell In xRange.Cells
bHidden = (xCell.EntireColumn.Hidden) Or (xCell.EntireRow.Hidden)

If Not bHidden Then
If TypeName(xBuildRange) = "Nothing" Then
Set xBuildRange = xCell
Else
Set xBuildRange = Union(xBuildRange, xCell)
End If
End If
Next xCell

Set VisRange = xBuildRange
End Function

You can then perform a SUM on the visible cells in range A1:A100 by entering the formula =SUM(VISRANGE(A1:A100)).

Regards,
MB

Ignore Hidden Rows on Sum Function on Excel XP
 

Hiding/unhiding a row will force a recalc in xl2003, but I think that's because
of the added parms in =subtotal().

I don't have earlier versions of excel to test on, but if F9 didn't force a
recalc, then maybe using ctrl-alt-f9 would be more effective.

In either case, I'd force a recalc before I trusted either procedures.

BizMark wrote:

The problem with UDFs is that unless one of its parameters changes, it
will not recalculate. By parameters changing, we mean values; not the
format properties of the cells containing the values.

Therefore, a change of any format (including row height; a row height
of 0 indicates 'hidden') will not recalculate.

Even pressing F9 won't change it. One way around this is to add a
dummy date parameter to the function, and simply pass NOW() to it. As
time is constantly changing, the formula will always be earmarked by
Excel as 'requiring recalculation' each time F9 is pressed or other
formulae are recalculated. The formula itself doesn't actually need to
do anything with that parameter, but as long as it's declared, it will
recalculate.

Notwithstanding all the above, I would say the best thing to do is use
a routine which passes back a RANGE object containing only the visible
cells. That way, you can do anything you want with those cells - not
just SUM. The problem with the above example is that you would have to
create a similar function for every possible type of operation you might
want - multiplying, averaging, vlookup, etc.

The following routine however allows you to specify a range as
VISRANGE(address) and pass it to any built-in (or user-defined)
function which accepts a range parameter.

Public Function VisRange(xRange As Range) As Range
Dim xBuildRange As Range

For Each xCell In xRange.Cells
bHidden EQUALS (xCell.EntireColumn.Hidden) Or
(xCell.EntireRow.Hidden)

If Not bHidden Then
If TypeName(xBuildRange) EQUALS "Nothing" Then
Set xBuildRange EQUALS xCell
Else
Set xBuildRange EQUALS Union(xBuildRange, xCell)
End If
End If
Next xCell

Set VisRange EQUALS xBuildRange
End Function

You can then perform a SUM on the visible cells in range A1:A100 by
entering the formula =SUM(VISRANGE(A1:A100)).

Regards,
MB

Dave Peterson Wrote:
If the formula returns a number, then I think you did everything ok.

But if you've hidden/unhidden any rows/columns, then formula may not
have
recalculated.

Try hitting F9 to see if that helps.



Carol wrote:

Hi Me Again

I've got the spreadsheet to accept SUMVISIBLE and to show a total now
but
it's not correct it adds in the hidden rows as well

What am I doing wrong?

Thanks

"Dave Peterson" wrote:

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


--

Dave Peterson

--
BizMark

--

Dave Peterson