
Author 
Message 
smartA

Posted: Mon Sep 17 07:29:02 PDT 2007 
Top 
Excel Programming >> Summing while hiding?!
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
Excel42





papou

Posted: Mon Sep 17 07:29:02 PDT 2007 
Top 
Excel Programming >> Summing while hiding?!
Hello Jo
Use SUBTOTAL(109,A1:A4)
HTH
Cordially
Pascal
> 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
>





Haldun

Posted: Mon Sep 17 07:40:00 PDT 2007 
Top 
Excel Programming >> Summing while hiding?!
Try
=SUBTOTAL(109,A1:A4)

Haldun Alay
> 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
>





Jo

Posted: Mon Sep 17 08:01:31 PDT 2007 
Top 
Excel Programming >> Summing while hiding?!
> Try
> =SUBTOTAL(109,A1:A4)
>
> 
> Haldun Alay
>
>
>
>
> > 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 Hide quoted text 
>
>  Show quoted text 
What if I want to get average instead of sum?
Thanks in advance,
Mike





Gary

Posted: Mon Sep 17 08:05:47 PDT 2007 
Top 
Excel Programming >> Summing while hiding?!
look up subtotal in help and it will show you. use 101 instead of 109.

Gary
>> Try
>> =SUBTOTAL(109,A1:A4)
>>
>> 
>> Haldun Alay
>>
>>
>>
>>
>> > 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 Hide quoted text 
>>
>>  Show quoted text 
>
> What if I want to get average instead of sum?
>
> Thanks in advance,
> Mike
>





Bill

Posted: Mon Sep 17 22:16:47 PDT 2007 
Top 
Excel Programming >> Summing while hiding?!
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!

Regards,
Bill Renaud






