Sum of a column excluding hidden rows
 

How do I sum many rows within a column but exclude hidden colums from the
calculation?

Thank you!

Sum of a column excluding hidden rows
 

Sorry, I meant exclude hidden rows from the calc, not columns.

"mnwild1" wrote:

How do I sum many rows within a column but exclude hidden colums from the
calculation?

Thank you!

Sum of a column excluding hidden rows
 

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!

Sum of a column excluding hidden rows
 

Hello Gord.
I am using Excel 2007. I have manually hidden the rows I want to exclude
from the calculation but when I use the autosum, it counts the hidden rows.
How can I sum the column but not count the hidden rows?

Thank you!!

"Gord Dibben" wrote:

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!

Sum of a column excluding hidden rows
 

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!

Sum of a column excluding hidden rows
 

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!

Sum of a column excluding hidden rows
 

Try "Gord Dibben" formula will work your manually hidden rows with your XL2007

=SUBTOTAL(109,your data range)


"mnwild1" wrote:

Hello Gord.
I am using Excel 2007. I have manually hidden the rows I want to exclude
from the calculation but when I use the autosum, it counts the hidden rows.
How can I sum the column but not count the hidden rows?

Thank you!!

"Gord Dibben" wrote:

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!

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!

Sum of a column excluding hidden rows
 

Biff,

This is a cool trick. Do you have any pointer to documentation of
GET.CELL?

Kostis

On Jun 22, 6:29 am, "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!

Sum of a column excluding hidden rows
 

Don't use the Autosum Button.


Gord

On Thu, 21 Jun 2007 20:14:00 -0700, mnwild1
wrote:

Hello Gord.
I am using Excel 2007. I have manually hidden the rows I want to exclude
from the calculation but when I use the autosum, it counts the hidden rows.
How can I sum the column but not count the hidden rows?

Thank you!!

"Gord Dibben" wrote:

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!

Sum of a column excluding hidden rows
 

Here's a link to the compiled help file that you can install:

http://tinyurl.com/ydght4

I have it as a text file that I got from Bob Phillips a few years ago:

http://cjoint.com/?gxaoQpmgiy

Biff

"vezerid" wrote in message
oups.com...
Biff,

This is a cool trick. Do you have any pointer to documentation of
GET.CELL?

Kostis

On Jun 22, 6:29 am, "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!