How do I specify a variable Excel range?

excel worksheet-function excel-2011
Anirvan picture Anirvan · Jun 4, 2011 · Viewed 59.9k times · Source

I'd like to be able to dynamically specify a range in Excel, based on row/column variables.

Let's say I have a file with contents that look sort of like this:

   A   B   C   D   E
1  10  11  12  13  14
2  51  52  53  54  55

If I wanted to sum the items in row 1, columns 2-4 (i.e. 11 + 12 + 13), how would I specify that?

If I were doing it by hand, I'd type:

=SUM(B1:D1)

...but how do I programatically generate that range definition on the fly, knowing only the wanted row (1) and column numbers (2-4)?

=SUM(????)

Thanks in advance for your help!

(I'm using Microsoft Excel 2011 for Mac, so Excel VBA/macro-based solutions won't work for me.)

Answer

user2347836 picture user2347836 · May 3, 2013

I had the same need--looks like the OFFSET function will let you do that.

So for the above:

=SUM(OFFSET(A1,0,1,1,3))

to break it down:

OFFSET(reference cell,
       row offset from ref cell to start the range,
       col offset to start the range, height of range you want,
       width of range you want)

you can make the offsets zero if you want, or + to ref down, - to ref up

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

Read More