Ignore Hidden Rows in Sum Function?

[Jugglertwo]

I had an interesting question asked by a student today.
The have some hidden rows with some numbers in them
throughout a list.
With the remaining/unhidden rows, they want to use the sum
function to add up the numbers. The sum function adds the
hidden rows as they are include in the general range.

1) I realize that they could do =b2+b4+b10+......+b100 but
this takes a lot of time.
2) They want to use =sum(b2:b100)
3) They also will need to do other calculations like
Average, Max and Min so putting them in with Functions
would be more efficient if possible.
4) Is there anyway to ignore the hidden cells in the
formula?
5) I thought at first that I might be able to use the
custom icon for Select Visible Cells in some way but it
doesn't seem to help at all.

Any suggestions would be greatly appreciated !
Thanks!
Jugglertwo

 

[Ken Wright]

=SUBTOTAL() function

In XL 2002 and before, it will only ignore rows hidden by a filter, but 2003
gives you the option to ignore manually hidden rows as well.

 

[JE McGimpsey]

have them take a look at SUBTOTAL() in XL Help.

 

[Dave Peterson]

With xl2002 and earlier, =subtotal() will ignore rows hidded by a filter.

In xl2003, there's additional options you can pass to =subtotal() to tell it to
ignore hidden rows.