Sum only visible cells

Is there a formula to sum only visible cells, so that if I hide a row the
formula result will change?

(In B6) =SUMIF(A1:A5,NOT( [cell row hidden] ),B1:B5)

Or will I need to write a function to do this?

TIA
Charlie

If you hide the rows with an advanced filter or an autofilter, you can use
the sum version of the SUBTOTAL function to display totals for visible items.

Example:

Col_A has Name
Col_B has Amount

A2: Name
A3: Bill
A4: Dave
etc

B2: 10
B3: 20
etc

B1: =SUBTOTAL(9,B2:B10)
intially returns 30.
(The 9 in the SUBTOTAL function tells Excel to ADD the amounts, other
options are AVERAGE, MIN, MAX...etc....check Excel Help)

If you autofilter to only show Bill, the formula wil return 10.

Does that help?

***********
Regards,
Ron


"Charlie" wrote:

> Is there a formula to sum only visible cells, so that if I hide a row the
> formula result will change?
>
> (In B6) =SUMIF(A1:A5,NOT( [cell row hidden] ),B1:B5)
>
> Or will I need to write a function to do this?
>
> TIA
> Charlie

In Excel 2003, subtotal has options to ignore any hidden row - not just
those rows hidden by a filter.

--
Regards,
Tom Ogilvy

"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> If you hide the rows with an advanced filter or an autofilter, you can use
> the sum version of the SUBTOTAL function to display totals for visible
items.
>
> Example:
>
> Col_A has Name
> Col_B has Amount
>
> A2: Name
> A3: Bill
> A4: Dave
> etc
>
> B2: 10
> B3: 20
> etc
>
> B1: =SUBTOTAL(9,B2:B10)
> intially returns 30.
> (The 9 in the SUBTOTAL function tells Excel to ADD the amounts, other
> options are AVERAGE, MIN, MAX...etc....check Excel Help)
>
> If you autofilter to only show Bill, the formula wil return 10.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
>
> "Charlie" wrote:
>
> > Is there a formula to sum only visible cells, so that if I hide a row
the
> > formula result will change?
> >
> > (In B6) =SUMIF(A1:A5,NOT( [cell row hidden] ),B1:B5)
> >
> > Or will I need to write a function to do this?
> >
> > TIA
> > Charlie

NICE! Thanks, Tom. I did not know that.
I briefly had XL2003 on my PC, but it wouldn't interface with a legacy
application I need to use. So, alas, I had to back down to XL2002.


***********
Regards,
Ron


"Tom Ogilvy" wrote:

> In Excel 2003, subtotal has options to ignore any hidden row - not just
> those rows hidden by a filter.
>
> --
> Regards,
> Tom Ogilvy
>
> "Ron Coderre" <[email protected]> wrote in message
> news:[email protected]...
> > If you hide the rows with an advanced filter or an autofilter, you can use
> > the sum version of the SUBTOTAL function to display totals for visible
> items.
> >
> > Example:
> >
> > Col_A has Name
> > Col_B has Amount
> >
> > A2: Name
> > A3: Bill
> > A4: Dave
> > etc
> >
> > B2: 10
> > B3: 20
> > etc
> >
> > B1: =SUBTOTAL(9,B2:B10)
> > intially returns 30.
> > (The 9 in the SUBTOTAL function tells Excel to ADD the amounts, other
> > options are AVERAGE, MIN, MAX...etc....check Excel Help)
> >
> > If you autofilter to only show Bill, the formula wil return 10.
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> >
> > "Charlie" wrote:
> >
> > > Is there a formula to sum only visible cells, so that if I hide a row
> the
> > > formula result will change?
> > >
> > > (In B6) =SUMIF(A1:A5,NOT( [cell row hidden] ),B1:B5)
> > >
> > > Or will I need to write a function to do this?
> > >
> > > TIA
> > > Charlie
>
>
>

Thanks, guys. Now all I need to do is get my employer to upgrade to 2003!

"Ron Coderre" wrote:

> NICE! Thanks, Tom. I did not know that.
> I briefly had XL2003 on my PC, but it wouldn't interface with a legacy
> application I need to use. So, alas, I had to back down to XL2002.
>
>
> ***********
> Regards,
> Ron
>
>
> "Tom Ogilvy" wrote:
>
> > In Excel 2003, subtotal has options to ignore any hidden row - not just
> > those rows hidden by a filter.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Ron Coderre" <[email protected]> wrote in message
> > news:[email protected]...
> > > If you hide the rows with an advanced filter or an autofilter, you can use
> > > the sum version of the SUBTOTAL function to display totals for visible
> > items.
> > >
> > > Example:
> > >
> > > Col_A has Name
> > > Col_B has Amount
> > >
> > > A2: Name
> > > A3: Bill
> > > A4: Dave
> > > etc
> > >
> > > B2: 10
> > > B3: 20
> > > etc
> > >
> > > B1: =SUBTOTAL(9,B2:B10)
> > > intially returns 30.
> > > (The 9 in the SUBTOTAL function tells Excel to ADD the amounts, other
> > > options are AVERAGE, MIN, MAX...etc....check Excel Help)
> > >
> > > If you autofilter to only show Bill, the formula wil return 10.
> > >
> > > Does that help?
> > >
> > > ***********
> > > Regards,
> > > Ron
> > >
> > >
> > > "Charlie" wrote:
> > >
> > > > Is there a formula to sum only visible cells, so that if I hide a row
> > the
> > > > formula result will change?
> > > >
> > > > (In B6) =SUMIF(A1:A5,NOT( [cell row hidden] ),B1:B5)
> > > >
> > > > Or will I need to write a function to do this?
> > > >
> > > > TIA
> > > > Charlie
> >
> >
> >

If your rows are manually hidden and you wan t to sum the visible rows
pre-2003, you can use this UDF

Function SumVisible(rng As Range)
Dim cell As Range
For Each cell In rng
If Not cell.EntireRow.Hidden Then
SumVisible = SumVisible + cell.Value
End If
Next cell
End Function


=SumVisible(A1:A10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Charlie" <[email protected]> wrote in message
news:[email protected]...
> Thanks, guys. Now all I need to do is get my employer to upgrade to 2003!
>
> "Ron Coderre" wrote:
>
> > NICE! Thanks, Tom. I did not know that.
> > I briefly had XL2003 on my PC, but it wouldn't interface with a legacy
> > application I need to use. So, alas, I had to back down to XL2002.
> >
> >
> > ***********
> > Regards,
> > Ron
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > In Excel 2003, subtotal has options to ignore any hidden row - not
just
> > > those rows hidden by a filter.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Ron Coderre" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > If you hide the rows with an advanced filter or an autofilter, you
can use
> > > > the sum version of the SUBTOTAL function to display totals for
visible
> > > items.
> > > >
> > > > Example:
> > > >
> > > > Col_A has Name
> > > > Col_B has Amount
> > > >
> > > > A2: Name
> > > > A3: Bill
> > > > A4: Dave
> > > > etc
> > > >
> > > > B2: 10
> > > > B3: 20
> > > > etc
> > > >
> > > > B1: =SUBTOTAL(9,B2:B10)
> > > > intially returns 30.
> > > > (The 9 in the SUBTOTAL function tells Excel to ADD the amounts,
other
> > > > options are AVERAGE, MIN, MAX...etc....check Excel Help)
> > > >
> > > > If you autofilter to only show Bill, the formula wil return 10.
> > > >
> > > > Does that help?
> > > >
> > > > ***********
> > > > Regards,
> > > > Ron
> > > >
> > > >
> > > > "Charlie" wrote:
> > > >
> > > > > Is there a formula to sum only visible cells, so that if I hide a
row
> > > the
> > > > > formula result will change?
> > > > >
> > > > > (In B6) =SUMIF(A1:A5,NOT( [cell row hidden] ),B1:B5)
> > > > >
> > > > > Or will I need to write a function to do this?
> > > > >
> > > > > TIA
> > > > > Charlie
> > >
> > >
> > >