How do I specify a variable Excel range

0 votes

Excel should allow me to dynamically define a range based on row/column variables. Say I have a file with information in it that is similar to this:

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

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

Using a manual keyboard, I would type:

=SUM(B1:D1)

But given that I only know the desired row (1) and column (2-4) numbers, how can I programmatically build that range description instantly?

=SUM(????)
Oct 30 in Others by Kithuzzz
• 20,660 points
32 views

1 answer to this question.

0 votes

Try this:

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

answered Oct 30 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How do I protect all worksheet in an Excel workbook with a single click?

VBA Code : Dim ws as Worksheet Dim pwd ...READ MORE

answered Oct 22 in Others by narikkadan
• 37,660 points
29 views
0 votes
1 answer

How do I combine the first character of a cell with another cell in Excel?

Try this: =CONCATENATE(LEFT(A1,1), B1) READ MORE

answered Nov 7 in Others by narikkadan
• 37,660 points
25 views
0 votes
1 answer

How do I insert a WebP-image (".jpg") in Excel using VBA?

It's not currently on the list of ...READ MORE

answered Nov 15 in Others by narikkadan
• 37,660 points
28 views
0 votes
1 answer

How do I get an Excel range using row and column numbers in VSTO / C#?

Use: int countRows = xlWorkSheetData.UsedRange.Rows.Count; int countColumns = xlWorkSheetData.UsedRange.Columns.Count; object[,] ...READ MORE

answered Nov 17 in Others by narikkadan
• 37,660 points
25 views
0 votes
1 answer
0 votes
1 answer

Excel formula to get cell color

Add a Name(any valid name) in Excel's ...READ MORE

answered Oct 23 in Others by narikkadan
• 37,660 points
57 views
0 votes
1 answer
0 votes
1 answer

Excel shared formula expansion

You can use Linq I would start ...READ MORE

answered Nov 7 in Others by narikkadan
• 37,660 points
30 views
0 votes
1 answer
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP