Summing while hiding?!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jo - 17 Sep 2007 15:20 GMT

Hi,

Say I have this in column a starting at row 1:

12
34
14
41

=Sum(A1:A4)=101

How can I make it behave smarter when I hide rows? So, for example,
say I decided to hide row 2:

12
14
41

=Sum(A1:A4)=67

When I did try it I still get 101!?

Thanks,
Jo

Reply to this Message

papou - 17 Sep 2007 15:29 GMT

Hello Jo
Use SUBTOTAL(109,A1:A4)

HTH
Cordially
Pascal

> Hi,
>
[quoted text clipped - 20 lines]
> Thanks,
> Jo

Reply to this Message

Haldun Alay - 17 Sep 2007 15:40 GMT

Try
=SUBTOTAL(109,A1:A4)

--
Haldun Alay

"Jo" <mas_it@hotmail.com>, haber iletisinde sunlari
yazdi:1190038842.433668.201430@k79g2000hse.googlegroups.com...

> Hi,
>
[quoted text clipped - 20 lines]
> Thanks,
> Jo

Reply to this Message

Jo - 17 Sep 2007 16:01 GMT

> Try
> =SUBTOTAL(109,A1:A4)
[quoted text clipped - 31 lines]
>
> - Show quoted text -

What if I want to get average instead of sum?

Thanks in advance,
Mike

Reply to this Message

Gary Keramidas - 17 Sep 2007 16:05 GMT

look up subtotal in help and it will show you. use 101 instead of 109.

Signature

Gary

>> Try
>> =SUBTOTAL(109,A1:A4)
[quoted text clipped - 36 lines]
> Thanks in advance,
> Mike

Reply to this Message

Bill Renaud - 18 Sep 2007 06:17 GMT

Note to Jo:
In Excel 2000 anyway, it must be a list or a database, so you cannot
start the data on row 1. The hidden rows must be the result of filtering
the rows.

Note to all:
In Excel 2000, therefore, the formula for SUM would be (note the
different function number):
=SUBTOTAL(9,A2:A5)

and the formula for AVERAGE would be (again Excel 2000):
=SUBTOTAL(1,A2:A5)

I guess Microsoft must have expanded the function numbers from Excel
2000 to Excel 2007! They must have an import wizard to convert older
worksheets to newer ones upon opening them!

Signature

Regards,
Bill Renaud

Reply to this Message