Adding a group of cells - variable group

[erc1452]

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?

 

[XLGibbs]

I am not sure I follow your intent properly..

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
	A	B	C	D
1	3	8
2	Data	Data2	Data3
3	10			50
4	10
5	10
6	10
7	10
8	10
9	10
10	10
11	10
12	10
13	10
14	10
15	10
16	10
17	10
18
Sheet1

 

[erc1452]

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

 

[XLGibbs]

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]

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

 

[XLGibbs]

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