Summing while hiding?!  
Author Message
smartA





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: 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