# Sum cells referred to by row & column numbers taken from different cells, without using VBA

My problem is quite simple but describing it is somewhat complicated.

I have four cells containing four integers, which signify row and column numbers of two cells. E.g.

``````     A    B
1    54   23
2    154  123
``````

So we have two cells:

• Cell #1: row 54, column 23 and
• Cell #2 row 154, column 123.

I want to sum the contents of all cells between Cell #1 and Cell #2. Of course, I want references to cells A1, A2, B1, B2 containing the relevant values, not the actual values. Say, I want to put this sum in cell C3.

In VBA it would be simple (albeit lengthy):

``````Range("C3").Cells = WorksheetFunction.Sum( _
Range(Cells(Cells(1,1),Cells(1,2)), _
Cells(Cells(2,1),Cells(2,2))))
``````

How to do it without VBA?

I tried something like `=SUM(ADDRESS(A1,B1),ADDRESS(A2,B2))`. My problem is, using ADDRESS() returns text. If I place this text in SUM(), it returns an error.

[Edited - I originally misread the question]

One way to do this would be to use the `OFFSET` function to locate the range you want.

``````=SUM(OFFSET(A1,A1-1,B1-1,A2-A1+1,B2-B1+1))
``````

`OFFSET(A1,A1-1,B1-1)` represents a one cell range whose row and column numbers are the values in A1 and A2. The last two parameters (`A2-A1+1` and `B2-B1+1`) are the number of rows and columns in the new range.

• Much more simple than mine. - Use this one. – B540Glenn May 11 '18 at 14:30
• Perfect and simple! I need to review the OFFSET function. Sorry guys, I can't upvote your posts (too low reputation) Blackwood & @B540Glenn – vpprof May 11 '18 at 15:42

Indirect and Address are volatile use INDEX:

``````=SUM(INDEX(A:AAA,A1,B1):INDEX(A:AAA,A2,B2))
``````

It ain't pretty buy it appears to work.

``````=SUM(INDIRECT(ADDRESS(ROW(INDIRECT("R"&A1&"C"&B1,FALSE)),COLUMN(INDIRECT("R"&A1&"C"&B1,FALSE)))):INDIRECT(ADDRESS(ROW(INDIRECT("R"&A2&"C"&B2,FALSE)),COLUMN(INDIRECT("R"&A2&"C"&B2,FALSE)))))
``````

It converts the cell contents to R1C1 style references (i.e. `R54C23`). From there, it converts it into a fixed reference (i.e. `\$W\$54`). It then uses that as in indirect to plug into the `SUM()` function.