LinkBack URL
About LinkBacksIs there a easy way to sum a column of numbers in excel and ignore hidden rows?
Is there a easy way to sum a column of numbers in excel and ignore hidden rows?
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
"CLPoulos" wrote:
> Is there a easy way to sum a column of numbers in excel and ignore hidden rows?
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
"Elkar" wrote:
> 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
>
>
> "CLPoulos" wrote:
>
> > Is there a easy way to sum a column of numbers in excel and ignore hidden rows?
You can't. SUBTOTAL only excludes rows hidden by filters.
"CLPoulos" <CLPoulos@discussions.microsoft.com> wrote in message
news:627A56F9-0D92-404B-BE9B-E65A345A75C4@microsoft.com...
>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
>
> "Elkar" wrote:
>
>> 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
>>
>>
>> "CLPoulos" wrote:
>>
>> > Is there a easy way to sum a column of numbers in excel and
>> > ignore hidden rows?
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.
"CLPoulos" wrote:
> Is there a easy way to sum a column of numbers in excel and ignore hidden rows?
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" wrote:
> You can't. SUBTOTAL only excludes rows hidden by filters.
>
>
> "CLPoulos" <CLPoulos@discussions.microsoft.com> wrote in message
> news:627A56F9-0D92-404B-BE9B-E65A345A75C4@microsoft.com...
> >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
> >
> > "Elkar" wrote:
> >
> >> 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
> >>
> >>
> >> "CLPoulos" wrote:
> >>
> >> > Is there a easy way to sum a column of numbers in excel and
> >> > ignore hidden rows?
>
>
>
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
"Elkar" <Elkar@discussions.microsoft.com> wrote in message
news:1667AD75-445C-420D-BDFF-B9EDF2E87133@microsoft.com...
> 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" wrote:
>
>> You can't. SUBTOTAL only excludes rows hidden by filters.
>>
>>
>> "CLPoulos" <CLPoulos@discussions.microsoft.com> wrote in
>> message
>> news:627A56F9-0D92-404B-BE9B-E65A345A75C4@microsoft.com...
>> >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
>> >
>> > "Elkar" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> "CLPoulos" wrote:
>> >>
>> >> > Is there a easy way to sum a column of numbers in excel
>> >> > and
>> >> > ignore hidden rows?
>>
>>
>>
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.
Chip Pearson wrote:
>
> 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
>
> "Elkar" <Elkar@discussions.microsoft.com> wrote in message
> news:1667AD75-445C-420D-BDFF-B9EDF2E87133@microsoft.com...
> > 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" wrote:
> >
> >> You can't. SUBTOTAL only excludes rows hidden by filters.
> >>
> >>
> >> "CLPoulos" <CLPoulos@discussions.microsoft.com> wrote in
> >> message
> >> news:627A56F9-0D92-404B-BE9B-E65A345A75C4@microsoft.com...
> >> >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
> >> >
> >> > "Elkar" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >> "CLPoulos" wrote:
> >> >>
> >> >> > Is there a easy way to sum a column of numbers in excel
> >> >> > and
> >> >> > ignore hidden rows?
> >>
> >>
> >>
--
Dave Peterson
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" <CLPoulos@discussions.microsoft.com> wrote in message
news:147C4E4F-07B3-49F7-8898-665EA04E935C@microsoft.com...
> Is there a easy way to sum a column of numbers in excel and ignore hidden
rows?
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
"Dave Peterson" wrote:
> 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.
>
> Chip Pearson wrote:
> >
> > 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
> >
> > "Elkar" <Elkar@discussions.microsoft.com> wrote in message
> > news:1667AD75-445C-420D-BDFF-B9EDF2E87133@microsoft.com...
> > > 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" wrote:
> > >
> > >> You can't. SUBTOTAL only excludes rows hidden by filters.
> > >>
> > >>
> > >> "CLPoulos" <CLPoulos@discussions.microsoft.com> wrote in
> > >> message
> > >> news:627A56F9-0D92-404B-BE9B-E65A345A75C4@microsoft.com...
> > >> >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
> > >> >
> > >> > "Elkar" wrote:
> > >> >
> > >> >> 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
> > >> >>
> > >> >>
> > >> >> "CLPoulos" wrote:
> > >> >>
> > >> >> > Is there a easy way to sum a column of numbers in excel
> > >> >> > and
> > >> >> > ignore hidden rows?
> > >>
> > >>
> > >>
>
> --
>
> Dave Peterson
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks