sum values in a column only for unhidden rows?

L

lindasf

If I have a list of rows (some of which are hidden) is there a way t
sum values in a column and IGNORE the values in the hidden rows - e.g
only sum the values in that column for rows that are NOT hidden.

Thx.

lindas
 
J

JulieD

Hi

i use a "user defined function" - copy & paste this into a module sheet in
your excel workbook (or personal.xls)

Function sumvisible(r1 As Range)
sumvisible = 0
On Error Resume Next
For Each c In r1
If c.EntireRow.Hidden <> True Then
sumvisible = sumvisible + c.Value
End If
Next
End Function

then click in the cell where you want the answer
choose your paste function wizard (fx icon) - from the categories choose
User Defined
click on the sumvisible function, specify the range to sum and you'll get
the total of the visible cells only.

Cheers
JulieD
 
F

Frank Kabel

Hi
if you have hidden the rows using Filters try
=SUBTOTAL(9,A1:A100)

if you have Excel 2003 you could also use SUBTOTAL for manually hidden
rows. Try
=SUBTOTAL(109,A1:A100)
 
D

Dave Peterson

See one more reply at your first post.

lindasf < said:
If I have a list of rows (some of which are hidden) is there a way to
sum values in a column and IGNORE the values in the hidden rows - e.g.
only sum the values in that column for rows that are NOT hidden.

Thx.

lindasf
Click to expand...
 
Top