Summing while hiding?!

[Jo]

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

 

[papou]

Hello Jo
Use SUBTOTAL(109,A1:A4)

HTH
Cordially
Pascal

 

[Haldun Alay]

Try
=SUBTOTAL(109,A1:A4)

 

[Jo]

Try
=SUBTOTAL(109,A1:A4)

--
Haldun Alay

"Jo" <[email protected]>, haber iletisinde sunlari
yazdi:[email protected]...






- Show quoted text -

What if I want to get average instead of sum?

Thanks in advance,
Mike

 

[Gary Keramidas]

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

 

[Bill Renaud]

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!