Summing columns in Excel?

C

CLPoulos

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

Elkar

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
 
C

CLPoulos

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
 
M

Miguel Zapico

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

Elkar

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

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
 
D

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

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

CLPoulos said:
Is there a easy way to sum a column of numbers in excel and ignore hidden
Click to expand...
rows?
 
C

CLPoulos

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
 
Top