# Adding a group of cells - variable group

#### erc1452

##### New Member
I have a chart, and I have two other cells that change value. Based on the value of the 2 cells, it will determine which rows in the chart I add together - that value in another cell alltogether.

My chart is 15 rows in height. My 1st cell has value between 1 and 9. The second cell has a value between 1 and 6. What I need to do, is add the rows in the chart between the value of my 1st cell, and both cells added. (i.e. if the 1st cell is 3 and the 2nd cell is 5, I need to add rows 3 through 8 together on my chart)

Is there any way to do this?

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### XLGibbs

##### Well-known Member

You want to determine which of the 15 rows you get a total for by entering two numbers into separate cells. (in your example 3 and 8, meaning total rows 3 to 8 in your table)

This is easy by formula, but would not consolidate the data in your chart directly.

You would have to have the chart pointing to the result of the formula row outside your 15 row chart/table.

The formula you could use is:

=Sum(OFFSET(top left cell of table, cell of first row, 0, cell of last row - cell of first row,0)

in the above syntax, I assume your table starts in A2, and you put the first number in A1 and the 2nd in B2

=Sum(OFFSET(A2,A1,0,B2-A1,1)

which in your example would calculate as follows:

Sum(OFFSET(A2, 3, 0, 5,1)

which in English means:

From Cell A2 go down 3 rows (so it would go from Row 2 to Row 5), and make a range which is (8-3) rows "high" or 5 rows and one column wide

Basically laid out below:
Book1
ABCD
138
31050
410
510
610
710
810
910
1010
1110
1210
1310
1410
1510
1610
1710
18
Sheet1

#### erc1452

##### New Member
Not quite, my chart is on one worksheet, and my cells are on another.

#### XLGibbs

##### Well-known Member
The you can point to the cells on your other sheet. The syntax of the formula would remain the same.

You can type the formula, and as you are getting to the cell references, just click in the cell you want it to point to (or range of cells for the ranges), and it will work the same.

#### erc1452

##### New Member
Thanks much, I figured it out finally - that formula is a bit different that what I am used to

#### XLGibbs

##### Well-known Member
I figured, which is why I tried to lay it out in english. It is a quite useful formula though. Glad to have helped you figure it out

Replies
17
Views
457
Replies
0
Views
125
Replies
4
Views
736
Replies
1
Views
363
Replies
1
Views
246

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,440
Messages
5,831,643
Members
430,079
Latest member
lucasabreueng

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back