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)) ??

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)) ??

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)) ??

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)) ??