Summing up a variable range of cells

**Nerdio** · 04-24-2013, 02:33 PM

I am sure this is not difficult, I ma just struggling with the syntax.

On a worksheet I want to be able to specify two rows as row numbers. rowA and rowB.

Then elsewhere I want to be able to write a formula that sums up the cells in a fixed column on a different sheet between those two rows.

Something like this....

SUM(CELL('Sheet1'ArowA, 'Sheet1'ArowB)

Which is summing up the cells between Row A Column A on Sheet 1 and Row B Column A on Sheet 1.

I think the use of the Cell function to specify the cells would do, but I am struggling with the syntax to include a fixed column in the cell reference and a varaiable (other cell) for the row.

I hope my requirement makes sense, as this would save me a lot of tedious work.

Thanks in anticipation.

(I will keep hacking away, and if I crack it will post the answer here)

**Lemice** · 04-24-2013, 02:42 PM

Hello,

You can do it with SUM function, for example you wanted to sum every cell within row 2 and row 9, from column A to column B, it will be like this
=SUM(A2:B9)
Column first, and row number second. You can change the cell Reference it to fit your data range.

**Nerdio** · 04-24-2013, 02:46 PM

Thanks for the reply, but that's not quite what I want. Maybe I wasn't clear.

To take your example..

I want to specify the 2 in A2 in one cell, and the 9 in B9 in a different cell

The idea being that by changing the value in two cells I can change the scope of the range. The reason why this is important is because I will do this sum for different columns in many places and I want to change the row range easily without editing multiple cells.

**Lemice** · 04-24-2013, 02:58 PM

I see, then you can use this set of formulas:
Assuming you have the value of starting range (in your example, 2) in D1, use this formula in E1

=ADDRESS(SMALL(IF($A$1:$B$12=$D1,ROW($A$1:$B$12)),1),SMALL(IF($A$1:$B$12=D1,COLUMN($A$1:$B$12)),1))

This will return the Cell reference containing that value (closest to the left and up - having smallest row and column number as possible)
And you have the ending range (in your example, 9) in D2, use this formula in E2

=ADDRESS(LARGE(IF($A$1:$B$12=$D2,ROW($A$1:$B$12)),1),LARGE(IF($A$1:$B$12=D2,COLUMN($A$1:$B$12)),1))

This will return the Cell Reference containing that value (furthest to the right and down - having largest row and column number as possible)
And this one to calculate the sum of that range using E1 and E2

=SUM(INDIRECT(E1&":"&E2))

Here is a sample file.

**Nerdio** · 04-24-2013, 03:03 PM

Thanks Lem, I have just found my own solution (a lot of trial and error). This is my solution

=SUM(INDIRECT("Totals!S"&H2):INDIRECT("Totals!S"&H3))

If H2 = 10 and H3 = 20 then this is equivalent to

SUM('Totals'!S10:'Totals'!S20)

Thanks again

**Lemice** · 04-24-2013, 03:07 PM

I see, so you only have to look it up in a column, I went ahead of myself and thought you might have more than 1 columns ...

Still, I'm glad that you have found a solution best fit to your problem.

Have a great day.