Summing columns in Excel?

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Chip Pearson]

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

 

[Guest]

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.

 

[Guest]

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]

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

 

[Dave Peterson]

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.

 

[Saruman]

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

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

rows?

 

[Guest]

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