Ignore Hidden Rows on Sum Function on Excel XP

[Guest]

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]

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

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

 

[Guest]

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

Anybody else any views please???

Carol

 

[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.

 

[Guest]

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]

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.

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

 

[Guest]

Thanks for your help I'll have a try

Carol

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.

 

[Guest]

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

Thanks for your help I'll have a try

Carol

 

[Guest]

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]

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.

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]

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.