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.

#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.



.

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.



.

=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

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

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