Sum of a column excluding hidden rows
Hi
Gord and TM have both told you the method. Have you tried it?
=SUBTOTAL(109,A1:A1000)
will add all of the values for unhidden rows in the range A1:A1000
--
Regards
Roger Govier
"mnwild1" wrote in message
...
Thank you for responding. Do you know of a way to do this with Excel
2007?
"T. Valko" wrote:
I have a kludge work-around for versions prior to Excel 2003 but it
requires
the use of a helper column.
A hidden row has a height property of 0 so all you need to do is sum
the
rows with a height 0.
Assume the range of interest is A1:A10 but several of those rows are
hidden.
Create this named formula:
InsertNameDefine
Name: Height
Refers to: =GET.CELL(17,INDIRECT("RC",FALSE))
OK
Enter this formula in B1 and copy down to B10:
=Height
It will also be copied to the hidden rows.
Now, to get the sum of only the visible rows in A1:A10
=SUMIF(B1:B10,"0",A1:A10)
Note that hidding or unhidding rows/columns does not trigger a
calculation.
If all rows are visible and then you hide some the formula will not
show the
correct result until a calculation takes place. You can either wait
for some
other action that triggers an automatic calculation or you can hit F9
to
force a calculation.
Like I said, kludge, but it's an option!
Biff
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Which version of Excel and how are the rows hidden?
Excel 2003 has added a function that subtotals manually hidden
and/or
filtered
rows.
If 2003 use the formula =SUBTOTAL(109,rangetosum) to sum all
hidden rows
no
matter the method.
In earlier versions if rows are hidden by Filtering then use this.
=SUBTOTAL(9,rangetosum)
If an earlier version and rows are hidden manually select the range
and
F5SpecialVisible cells only and OK.
Copy/paste to an unused range and sum them there.
Gord Dibben MS Excel MVP
On Thu, 21 Jun 2007 19:40:01 -0700, mnwild1
wrote:
How do I sum many rows within a column but exclude hidden colums
from the
calculation?
Thank you!
|