Is there a easy way to sum a column of numbers in excel and ignore hidden rows?

Take a look at the SUBTOTAL function. It can be set to include/exclude
hidden rows.

=SUBTOTAL(109,A1:A100)

This would total only the visible rows in the range A1:A100.

HTH,
Elkar


"CLPoulos" wrote:

Is there a easy way to sum a column of numbers in excel and ignore hidden rows?

I checked out the SUBTOTAL function and it looks like it only ignores hidden
cells if they have been filtered out. I am not filtering this information,
just hiding the rows. Exactly how do I set the SUBTOTAL function to exclude
hidden rows that have not been filtered?

Thanks,
Cathy

"Elkar" wrote:

Take a look at the SUBTOTAL function. It can be set to include/exclude
hidden rows.

=SUBTOTAL(109,A1:A100)

This would total only the visible rows in the range A1:A100.

HTH,
Elkar


"CLPoulos" wrote:

Is there a easy way to sum a column of numbers in excel and ignore hidden rows?

You can't. SUBTOTAL only excludes rows hidden by filters.


"CLPoulos" wrote in message
...
I checked out the SUBTOTAL function and it looks like it only
ignores hidden
cells if they have been filtered out. I am not filtering this
information,
just hiding the rows. Exactly how do I set the SUBTOTAL
function to exclude
hidden rows that have not been filtered?

Thanks,
Cathy

"Elkar" wrote:

Take a look at the SUBTOTAL function. It can be set to
include/exclude
hidden rows.

=SUBTOTAL(109,A1:A100)

This would total only the visible rows in the range A1:A100.

HTH,
Elkar


"CLPoulos" wrote:

Is there a easy way to sum a column of numbers in excel and
ignore hidden rows?

You can use an UDF like:

Function SkipHidden(sRange As Range)
Application.Volatile
Dim result
For Each cell In sRange.Cells
If Not Rows(cell.Row).Hidden Then
result = result + cell.Value
End If
Next
SkipHidden = result
End Function

Hope this helps,
Miguel.

"CLPoulos" wrote:

Is there a easy way to sum a column of numbers in excel and ignore hidden rows?

That's strange. SUBTOTAL works fine on Hidden or Filtered Rows for me.

We are talking about the SUBTOTAL function right? Not the Subtotals command
found on the Data Menu?

"Chip Pearson" wrote:

You can't. SUBTOTAL only excludes rows hidden by filters.


"CLPoulos" wrote in message
...
I checked out the SUBTOTAL function and it looks like it only
ignores hidden
cells if they have been filtered out. I am not filtering this
information,
just hiding the rows. Exactly how do I set the SUBTOTAL
function to exclude
hidden rows that have not been filtered?

Thanks,
Cathy

"Elkar" wrote:

Take a look at the SUBTOTAL function. It can be set to
include/exclude
hidden rows.

=SUBTOTAL(109,A1:A100)

This would total only the visible rows in the range A1:A100.

HTH,
Elkar


"CLPoulos" wrote:

Is there a easy way to sum a column of numbers in excel and
ignore hidden rows?

I just tried the SUBTOTAL function with hidden (not filtered)
rows, and it definitely included the hidden rows. What version of
Excel are you using? I'm in 2003.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Elkar" wrote in message
...
That's strange. SUBTOTAL works fine on Hidden or Filtered Rows
for me.

We are talking about the SUBTOTAL function right? Not the
Subtotals command
found on the Data Menu?

"Chip Pearson" wrote:

You can't. SUBTOTAL only excludes rows hidden by filters.


"CLPoulos" wrote in
message
...
I checked out the SUBTOTAL function and it looks like it only
ignores hidden
cells if they have been filtered out. I am not filtering
this
information,
just hiding the rows. Exactly how do I set the SUBTOTAL
function to exclude
hidden rows that have not been filtered?

Thanks,
Cathy

"Elkar" wrote:

Take a look at the SUBTOTAL function. It can be set to
include/exclude
hidden rows.

=SUBTOTAL(109,A1:A100)

This would total only the visible rows in the range
A1:A100.

HTH,
Elkar


"CLPoulos" wrote:

Is there a easy way to sum a column of numbers in excel
and
ignore hidden rows?

xl2003 added those 100 series of parms:

=SUBTOTAL(109,A1:A100)
instead of
=SUBTOTAL(9,A1:A100)

109 will ignore manually hidden rows.
9 will not ignore them.

Chip Pearson wrote:

I just tried the SUBTOTAL function with hidden (not filtered)
rows, and it definitely included the hidden rows. What version of
Excel are you using? I'm in 2003.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Elkar" wrote in message
...
That's strange. SUBTOTAL works fine on Hidden or Filtered Rows
for me.

We are talking about the SUBTOTAL function right? Not the
Subtotals command
found on the Data Menu?

"Chip Pearson" wrote:

You can't. SUBTOTAL only excludes rows hidden by filters.


"CLPoulos" wrote in
message
...
I checked out the SUBTOTAL function and it looks like it only
ignores hidden
cells if they have been filtered out. I am not filtering
this
information,
just hiding the rows. Exactly how do I set the SUBTOTAL
function to exclude
hidden rows that have not been filtered?

Thanks,
Cathy

"Elkar" wrote:

Take a look at the SUBTOTAL function. It can be set to
include/exclude
hidden rows.

=SUBTOTAL(109,A1:A100)

This would total only the visible rows in the range
A1:A100.

HTH,
Elkar


"CLPoulos" wrote:

Is there a easy way to sum a column of numbers in excel
and
ignore hidden rows?




--

Dave Peterson

An easier way, is to highlight the column you want to find the sum for then
look at the bottom right hand corner of Excel, about a third of the way in,
there will be the word Sum = and the total of the highlighted cells. This
does NOT include any hidden rows.

This word can also be right clicked to change its function to one of 6
different functions

KISS - Keep It Simple Stupid
--
Saruman
---------------------------------------------------------------------------
All Outgoing Mail Scanned By Norton Antivirus 2003
---------------------------------------------------------------------------

"CLPoulos" wrote in message
...
Is there a easy way to sum a column of numbers in excel and ignore hidden
rows?

Thank you all, you have been very helpful. Unfortunately, I have xl2002, so
the SUBTOTAL function does not work with hidden rows in this version. Guess
I'll have to upgrade!

Cathy

"Dave Peterson" wrote:

xl2003 added those 100 series of parms:

=SUBTOTAL(109,A1:A100)
instead of
=SUBTOTAL(9,A1:A100)

109 will ignore manually hidden rows.
9 will not ignore them.

Chip Pearson wrote:

I just tried the SUBTOTAL function with hidden (not filtered)
rows, and it definitely included the hidden rows. What version of
Excel are you using? I'm in 2003.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Elkar" wrote in message
...
That's strange. SUBTOTAL works fine on Hidden or Filtered Rows
for me.

We are talking about the SUBTOTAL function right? Not the
Subtotals command
found on the Data Menu?

"Chip Pearson" wrote:

You can't. SUBTOTAL only excludes rows hidden by filters.


"CLPoulos" wrote in
message
...
I checked out the SUBTOTAL function and it looks like it only
ignores hidden
cells if they have been filtered out. I am not filtering
this
information,
just hiding the rows. Exactly how do I set the SUBTOTAL
function to exclude
hidden rows that have not been filtered?

Thanks,
Cathy

"Elkar" wrote:

Take a look at the SUBTOTAL function. It can be set to
include/exclude
hidden rows.

=SUBTOTAL(109,A1:A100)

This would total only the visible rows in the range
A1:A100.

HTH,
Elkar


"CLPoulos" wrote:

Is there a easy way to sum a column of numbers in excel
and
ignore hidden rows?




--

Dave Peterson