1

I'm using Excel::Template to generate a series of Excel files via perl. However, I need to do a SUM function on the current Column. I know I can do

=SUM(3:15)

but that gives the sum of ALL columns in rows 3-15. Is there an easier way to do what I'm trying to do?

Glen Solsberry
  • 11,960
  • 15
  • 69
  • 94

2 Answers2

1
=sum(indirect(concatenate(address(<row_start>,column()),":")&address(<row_end>,column())))

gives me exactly what I need. Not exactly sure how it works, but found on MrExcel.com

Glen Solsberry
  • 11,960
  • 15
  • 69
  • 94
  • 2
    the column() function gets the number of the current column (so if you put "=column()" as the formula for cell A3, it will be "1"). The address() function returns an excel-friendly address string based on a row and column number (3,3 -> "$D$3"). Concatenate() joins strings together (the ":" makes the first and second address into a range reference). Indirect() changes a string like "A1:D1" to a cell/range reference of the intended string. And finally, sum() takes the range reference and adds up the values. – Kimball Robinson Jan 17 '11 at 21:02
0

For column C, 

=SUM(C3:C15)

Since =SUM(...) is just a string, you may have to parametrize the column if you don't know it before runtime. For instance

$str = "=SUM(" . col_char . "3:" . col_char . "15)";
ktm5124
  • 11,861
  • 21
  • 74
  • 119
  • The column character is not provided to me; otherwise, this would be a simple task. – Glen Solsberry Jan 17 '11 at 19:27
  • Excel VBA has a way of determining the active workbook, active worksheet, active column and active cell. It's likely your Perl API has this too. I'm sorry, though, that I misunderstood your question. – ktm5124 Jan 17 '11 at 19:30