2

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.

2

[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
2

Indirect and Address are volatile use INDEX:

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.