Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a range that changes
Hi,
I have a formula that sums the range Cell B1 = Sum(A1:$A$100) and this is copied down to cell B100, so Cell B33 = Sum(A33:$A$100) But I want the sum range to change, based on the value in cell C1. If C1 = 100, then the sum range will = Sum(A1:$A$100), Cell B33 = Sum(A33:$A$100) If Cell C1 = 300, then the sum range will = Sum(A1:$A$300) Cell B33 = Sum(A33:$A$300) I tried using indirect but I could not figure it out. Thanks for your help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a range that changes
Take the $ out of the equations. $ = absolutes, which tells excell that
those particular values do not change. "Jeff" wrote: Hi, I have a formula that sums the range Cell B1 = Sum(A1:$A$100) and this is copied down to cell B100, so Cell B33 = Sum(A33:$A$100) But I want the sum range to change, based on the value in cell C1. If C1 = 100, then the sum range will = Sum(A1:$A$100), Cell B33 = Sum(A33:$A$100) If Cell C1 = 300, then the sum range will = Sum(A1:$A$300) Cell B33 = Sum(A33:$A$300) I tried using indirect but I could not figure it out. Thanks for your help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a range that changes
"NUMBnut" wrote: Take the $ out of the equations. $ = absolutes, which tells excell that those particular values do not change. "Jeff" wrote: Hi, I have a formula that sums the range Cell B1 = Sum(A1:$A$100) and this is copied down to cell B100, so Cell B33 = Sum(A33:$A$100) But I want the sum range to change, based on the value in cell C1. If C1 = 100, then the sum range will = Sum(A1:$A$100), Cell B33 = Sum(A33:$A$100) If Cell C1 = 300, then the sum range will = Sum(A1:$A$300) Cell B33 = Sum(A33:$A$300) I tried using indirect but I could not figure it out. Thanks for your help That does not work because the last reference A300 must stay constant, and A1 is the cell that changes |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a range that changes
In using absolutes the entire equation will not change. You can use the fill
feature to fill down, but you will have to go back and change the values. Sorry, don't know a short cut for that. "Jeff" wrote: "NUMBnut" wrote: Take the $ out of the equations. $ = absolutes, which tells excell that those particular values do not change. "Jeff" wrote: Hi, I have a formula that sums the range Cell B1 = Sum(A1:$A$100) and this is copied down to cell B100, so Cell B33 = Sum(A33:$A$100) But I want the sum range to change, based on the value in cell C1. If C1 = 100, then the sum range will = Sum(A1:$A$100), Cell B33 = Sum(A33:$A$100) If Cell C1 = 300, then the sum range will = Sum(A1:$A$300) Cell B33 = Sum(A33:$A$300) I tried using indirect but I could not figure it out. Thanks for your help That does not work because the last reference A300 must stay constant, and A1 is the cell that changes |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a range that changes
This appears to do what you are asking for:
=SUM(INDIRECT("a" & ROW(A1) & ":a" & $C$1)) "Jeff" wrote: Hi, I have a formula that sums the range Cell B1 = Sum(A1:$A$100) and this is copied down to cell B100, so Cell B33 = Sum(A33:$A$100) But I want the sum range to change, based on the value in cell C1. If C1 = 100, then the sum range will = Sum(A1:$A$100), Cell B33 = Sum(A33:$A$100) If Cell C1 = 300, then the sum range will = Sum(A1:$A$300) Cell B33 = Sum(A33:$A$300) I tried using indirect but I could not figure it out. Thanks for your help |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a range that changes
Try this
=SUM(INDIRECT("A"&ROW()&":A"&C1)) -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... Hi, I have a formula that sums the range Cell B1 = Sum(A1:$A$100) and this is copied down to cell B100, so Cell B33 = Sum(A33:$A$100) But I want the sum range to change, based on the value in cell C1. If C1 = 100, then the sum range will = Sum(A1:$A$100), Cell B33 = Sum(A33:$A$100) If Cell C1 = 300, then the sum range will = Sum(A1:$A$300) Cell B33 = Sum(A33:$A$300) I tried using indirect but I could not figure it out. Thanks for your help |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a range that changes
Sorry that should be
=SUM(INDIRECT("A"&ROW()&":A"&$C$1)) -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... Hi, I have a formula that sums the range Cell B1 = Sum(A1:$A$100) and this is copied down to cell B100, so Cell B33 = Sum(A33:$A$100) But I want the sum range to change, based on the value in cell C1. If C1 = 100, then the sum range will = Sum(A1:$A$100), Cell B33 = Sum(A33:$A$100) If Cell C1 = 300, then the sum range will = Sum(A1:$A$300) Cell B33 = Sum(A33:$A$300) I tried using indirect but I could not figure it out. Thanks for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing a Range Omitting the Occasional #N/A | Excel Worksheet Functions | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
Can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel |