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, 2022 in Others by Kithuzzz
• 38,010 points
561 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, 2022 by narikkadan
• 63,700 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, 2022 in Others by narikkadan
• 63,700 points
645 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, 2022 in Others by narikkadan
• 63,700 points
1,109 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, 2022 in Others by narikkadan
• 63,700 points
560 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, 2022 in Others by narikkadan
• 63,700 points
1,404 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,700 points
3,277 views
0 votes
1 answer

Excel formula to get cell color

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

answered Oct 23, 2022 in Others by narikkadan
• 63,700 points
9,208 views
0 votes
1 answer

How to categorize/classify numbers from different ranges using Excel?

I wouldn't advise utilizing nested IFs because ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,700 points
3,090 views
0 votes
1 answer

Excel shared formula expansion

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

answered Nov 7, 2022 in Others by narikkadan
• 63,700 points
474 views
0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,700 points
1,516 views
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