Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Sum or Count using set range

I would like to do a sum or count formula between a specific range for column
totals. If I use for eg sum(A2:A10) or Sum(A2:$A$10) they are both affected
when a row is inserted and the 10 is now changed to 11.
Is there a formula that can be used so in A1 I have my start row value as 2
and B1 my end row value as 10, then build a Sum or Count formula as:
=Sum or =Count(A&Value(A1):A&Value(B1)) ??
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,081
Default Sum or Count using set range

one option is OFFSET(). With the #of rows to sum(not the ending cell) in B1

=SUM(OFFSET(A2,0,0,B1,1))

"Rayasiom" wrote:

I would like to do a sum or count formula between a specific range for column
totals. If I use for eg sum(A2:A10) or Sum(A2:$A$10) they are both affected
when a row is inserted and the 10 is now changed to 11.
Is there a formula that can be used so in A1 I have my start row value as 2
and B1 my end row value as 10, then build a Sum or Count formula as:
=Sum or =Count(A&Value(A1):A&Value(B1)) ??

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 158
Default Sum or Count using set range

One way is to use the INDIRECT function, which ignores inserts, copy/pastes,
etc:

=SUM(INDIRECT("A2:A10"))

"Rayasiom" wrote:

I would like to do a sum or count formula between a specific range for column
totals. If I use for eg sum(A2:A10) or Sum(A2:$A$10) they are both affected
when a row is inserted and the 10 is now changed to 11.
Is there a formula that can be used so in A1 I have my start row value as 2
and B1 my end row value as 10, then build a Sum or Count formula as:
=Sum or =Count(A&Value(A1):A&Value(B1)) ??

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Sum or Count using set range

Thanks Duke Carey and andy62. Both of the solutions work perfectly.

"andy62" wrote:

One way is to use the INDIRECT function, which ignores inserts, copy/pastes,
etc:

=SUM(INDIRECT("A2:A10"))

"Rayasiom" wrote:

I would like to do a sum or count formula between a specific range for column
totals. If I use for eg sum(A2:A10) or Sum(A2:$A$10) they are both affected
when a row is inserted and the 10 is now changed to 11.
Is there a formula that can be used so in A1 I have my start row value as 2
and B1 my end row value as 10, then build a Sum or Count formula as:
=Sum or =Count(A&Value(A1):A&Value(B1)) ??

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count dynamic range S Davis Excel Worksheet Functions 6 February 26th 07 08:51 PM
Count the how many different values in a range beechum1 Excel Worksheet Functions 3 February 12th 06 07:45 AM
How do I count in a range of dates? nancy Excel Worksheet Functions 2 November 23rd 05 12:03 PM
How to count dates within a certain range in a column with mutiple date range entries Krisjhn Excel Worksheet Functions 2 September 1st 05 01:59 PM
Count cells in one range based on parameters in another range dave roth Excel Worksheet Functions 2 March 29th 05 05:33 PM


All times are GMT +1. The time now is 05:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"