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?
 

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
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top