Adding a group of cells - variable group

erc1452

New Member
Joined
Aug 17, 2002
Messages
26
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?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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
ABCD
138
2DataData2Data3
31050
410
510
610
710
810
910
1010
1110
1210
1310
1410
1510
1610
1710
18
Sheet1
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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
Joined
Aug 17, 2002
Messages
26
Thanks much, I figured it out finally - that formula is a bit different that what I am used to
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,796
Messages
5,574,361
Members
412,589
Latest member
ArtBOM
Top