Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sum offset question
I need to sum a range based on the number on another cell
I use the following function to get that =SUM(OFFSET(J45,0,0,L12)) (basically summing J45 thru J49 since cell L12 contains value 5) I gives me the correct result from above formula when summing across rows. However when I change that formula to sum across columns and changing the same formula to =SUM(OFFSET(J45,0,0,0,L12)) to sum across columns it gives me a result #REF! What would be the correct formula to sum across columns. |
#2
|
|||
|
|||
#REF! means that the range is beyond the worksheet. In
which cell is your formula located? For example, if it were in IV1 and L12 = 5, you'll receive an error because there are no more columns beyond column IV. The error can also be caused if you delete rows and/or columns that the formula is referencing. HTH Jason Atlanta, GA -----Original Message----- I need to sum a range based on the number on another cell I use the following function to get that =SUM(OFFSET(J45,0,0,L12)) (basically summing J45 thru J49 since cell L12 contains value 5) I gives me the correct result from above formula when summing across rows. However when I change that formula to sum across columns and changing the same formula to =SUM(OFFSET(J45,0,0,0,L12)) to sum across columns it gives me a result #REF! What would be the correct formula to sum across columns. . |
#3
|
|||
|
|||
However if you look at the formula I want the cells J45 thru N45 summed both
within acceptable range within the worksheet Is there any other formula other than offset that can be used to sum across columns I basically transposed it to rows and achieved the desired result but was wondering what was wrong with my formula "Jason Morin" wrote in message ... #REF! means that the range is beyond the worksheet. In which cell is your formula located? For example, if it were in IV1 and L12 = 5, you'll receive an error because there are no more columns beyond column IV. The error can also be caused if you delete rows and/or columns that the formula is referencing. HTH Jason Atlanta, GA -----Original Message----- I need to sum a range based on the number on another cell I use the following function to get that =SUM(OFFSET(J45,0,0,L12)) (basically summing J45 thru J49 since cell L12 contains value 5) I gives me the correct result from above formula when summing across rows. However when I change that formula to sum across columns and changing the same formula to =SUM(OFFSET(J45,0,0,0,L12)) to sum across columns it gives me a result #REF! What would be the correct formula to sum across columns. . |
#4
|
|||
|
|||
=SUM(OFFSET(J45,0,0,1,L12)) =SUM(J45:INDEX(J45:IV45,L12)) sd Wrote: I need to sum a range based on the number on another cell I use the following function to get that =SUM(OFFSET(J45,0,0,L12)) (basically summing J45 thru J49 since cell L12 contains value 5) I gives me the correct result from above formula when summing across rows. However when I change that formula to sum across columns and changing the same formula to =SUM(OFFSET(J45,0,0,0,L12)) to sum across columns it gives me a result #REF! What would be the correct formula to sum across columns. -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=319477 |
#5
|
|||
|
|||
You need to correct the fourth parameter (height) in the offset function... =SUM(OFFSET(J45,0,0,1,L12)) Hope this helps! sd Wrote: I need to sum a range based on the number on another cell I use the following function to get that =SUM(OFFSET(J45,0,0,L12)) (basically summing J45 thru J49 since cell L12 contains value 5) I gives me the correct result from above formula when summing across rows. However when I change that formula to sum across columns and changing the same formula to =SUM(OFFSET(J45,0,0,0,L12)) to sum across columns it gives me a result #REF! What would be the correct formula to sum across columns. -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=319477 |
#6
|
|||
|
|||
Thanks guys it works now.
"Domenic" wrote in message ... You need to correct the fourth parameter (height) in the offset function... =SUM(OFFSET(J45,0,0,1,L12)) Hope this helps! sd Wrote: I need to sum a range based on the number on another cell I use the following function to get that =SUM(OFFSET(J45,0,0,L12)) (basically summing J45 thru J49 since cell L12 contains value 5) I gives me the correct result from above formula when summing across rows. However when I change that formula to sum across columns and changing the same formula to =SUM(OFFSET(J45,0,0,0,L12)) to sum across columns it gives me a result #REF! What would be the correct formula to sum across columns. -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=319477 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
basic pie chart question | Charts and Charting in Excel | |||
OFFSET and array formulae | Excel Discussion (Misc queries) | |||
Offset? | Excel Discussion (Misc queries) | |||
Have a question on scrolling sum with Excel. | Excel Worksheet Functions | |||
end of worksheet question | Excel Worksheet Functions |