Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional Range w/Sum
Ok..I've been trying to bang out a formula that will essentially do a sum on
a variable range within a certain column--Column A. The range will be determined by a value in another column--Column B. Example: If the value in cell B1 is between 1-2, the formula sums cells A1 and A2 and returns the value to, say, cell C2. Any advice on how to set up this kind of variable range (for lack of a better term) within a formula would be greatly appreciated. |
#2
|
|||
|
|||
One way:
C2 =IF(AND(B11,B1<2),SUM(A1:A2),"") In article , "Vlad" wrote: Ok..I've been trying to bang out a formula that will essentially do a sum on a variable range within a certain column--Column A. The range will be determined by a value in another column--Column B. Example: If the value in cell B1 is between 1-2, the formula sums cells A1 and A2 and returns the value to, say, cell C2. Any advice on how to set up this kind of variable range (for lack of a better term) within a formula would be greatly appreciated. |
#3
|
|||
|
|||
Look at the OFFSET function
Something like =SUM(OFFSET(A1,0,0,ROUNDUP(B1))) Maybe PC "Vlad" wrote in message ... Ok..I've been trying to bang out a formula that will essentially do a sum on a variable range within a certain column--Column A. The range will be determined by a value in another column--Column B. Example: If the value in cell B1 is between 1-2, the formula sums cells A1 and A2 and returns the value to, say, cell C2. Any advice on how to set up this kind of variable range (for lack of a better term) within a formula would be greatly appreciated. |
#4
|
|||
|
|||
In C2, you could use the formula:
=SUM(INDIRECT("A1:A" & B1)) So if B1 were 3, you would get the sum from A1:A3. HTH, Bernie MS Excel MVP "Vlad" wrote in message ... Ok..I've been trying to bang out a formula that will essentially do a sum on a variable range within a certain column--Column A. The range will be determined by a value in another column--Column B. Example: If the value in cell B1 is between 1-2, the formula sums cells A1 and A2 and returns the value to, say, cell C2. Any advice on how to set up this kind of variable range (for lack of a better term) within a formula would be greatly appreciated. |
#5
|
|||
|
|||
Another way:
=SUM(A1:INDEX(A:A,B1)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... In C2, you could use the formula: =SUM(INDIRECT("A1:A" & B1)) So if B1 were 3, you would get the sum from A1:A3. HTH, Bernie MS Excel MVP "Vlad" wrote in message ... Ok..I've been trying to bang out a formula that will essentially do a sum on a variable range within a certain column--Column A. The range will be determined by a value in another column--Column B. Example: If the value in cell B1 is between 1-2, the formula sums cells A1 and A2 and returns the value to, say, cell C2. Any advice on how to set up this kind of variable range (for lack of a better term) within a formula would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Define a range based on another named range | Excel Worksheet Functions | |||
Conditional data validation (using a filtered range?) | Excel Worksheet Functions | |||
How do I set a date range for conditional formatting in a macro? | Excel Worksheet Functions | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
HOW TO USE CONDITIONAL FORMATTING FROM ONE RANGE TO ANOTHER RANGE | Excel Worksheet Functions |