Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count dynamic range | Excel Worksheet Functions | |||
Count the how many different values in a range | Excel Worksheet Functions | |||
How do I count in a range of dates? | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions |