exclude hidden rows from sum

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ian - 17 Oct 2007 02:32 GMT

is there a way to exclude hidden rows when summing a column

eg: sum(A1:A4) but Row 3 is hidden so the result I want is =A1+A2+A4..

Thanks. Ian.

Reply to this Message

OssieMac - 17 Oct 2007 02:42 GMT

Hi Ian,

What method is used to hide the rows? If they are hidden due to Autofilter
then you can use:-

=SUBTOTAL(9,A1:A4)

Lookup subtotal for more parameter options.

Regards,

OssieMac

> is there a way to exclude hidden rows when summing a column
>
> eg: sum(A1:A4) but Row 3 is hidden so the result I want is =A1+A2+A4..
>
> Thanks. Ian.

Reply to this Message

OssieMac - 17 Oct 2007 02:53 GMT

Hi again Ian,

The following ignores hidden rows:-

=SUBTOTAL(109,A1:A4)

Regards,

OssieMac

> Hi Ian,
>
[quoted text clipped - 14 lines]
> >
> > Thanks. Ian.

Reply to this Message

Gord Dibben - 17 Oct 2007 03:01 GMT

Ossiemac

This function was introduced in Excel 2003.

Prior to that SUBTOTAL would function only on rows hidden by autofilter as
pointed out in your first post.

Gord Dibben MS Excel MVP

>Hi again Ian,
>
[quoted text clipped - 24 lines]
>> >
>> > Thanks. Ian.

Reply to this Message

Ian - 18 Oct 2007 00:05 GMT

The subtotal (109, [range]) worked perfectly (Excel 2003). Never knew the
function even existed. Thanks a lot guys. Ian.

> is there a way to exclude hidden rows when summing a column
>
> eg: sum(A1:A4) but Row 3 is hidden so the result I want is =A1+A2+A4..
>
> Thanks. Ian.

Reply to this Message

Rate this thread: