Ignore Hidden Rows in Sum Function?
 

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

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Jugglertwo" wrote in message
...
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

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


In article ,
"Jugglertwo" wrote:

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

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.



Jugglertwo wrote:

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

--

Dave Peterson