0

I primarily use LibreOffice Calc instead of Excel. When creating my spreadsheet, I relied upon the fact that the ADDRESS function was able to take a cell range and return an array of cell reference strings. But this doesn't work in Excel as the function doesn't work on arrays.

I have a table of values from which I want to sum selected columns in a row. The columns to select is named cell range mycols and the row is myrow, giving the following formula:

=SUMPRODUCT(INDIRECT(ADDRESS(myrow, mycols)))

For example:

  • ADDRESS returns {$A$2, $D$2, $B$2} with myrow=2 and mycols={1,4,2} (but only $A$2 in Excel).
  • INDIRECT returns the cell values {1,2,3}.
  • SUMPRODUCT returns 6 in LibreOffice, but 1 in Excel.

I tried using the RC notation instead and concatenating arrays, but concatenation isn't note element-wise so this doesn't work. I'd like not to use macros.

Improve this question
7
  • Assuming myCols = ={1,4,2} and not the string {1,4,2}: In Excel 2010, try entering this as an array formula by holding down ctrl+shift while hitting enter. In more recent versions of Excel with dynamic arrays, you can use =ADDRESS(myRow,myCols#)
    – Ron Rosenfeld
    Dec 4, 2022 at 1:01
  • I'd like to have it in a single formula, no temporary cells, as each cell of another large table contain this formula. ADDRESS has the same result with ctrl+shift+enter. I'd also like to maintain compatibility and LibreOffice doesn't seem to support dynamic arrays.
    – Nicolas
    Dec 4, 2022 at 1:04
  • 1
    The problem is with the ADDRESS function. If the ADDRESS formula in Excel 2010 still only returns the single value with ctrl+shift+enter, I cannot help you with an Excel formula solution. I do not have 2010 on my system so cannot test anything. Hopefully another will have a solution that works for you that does not involve upgrading all of your Excel installations.
    – Ron Rosenfeld
    Dec 4, 2022 at 1:33
  • Welcome! Nicolas, your story is incomplete. If you know the row number and the required columns, why don't you just write =SUM(A1;D1;B1) ? Tell us in more detail why you chose such a complex and unreliable method, what is the reason for such an approach to extracting values from a table. I wanted to suggest something like =SUMPRODUCT(INDEX($A$1:$D$40;myrow;mycols)) or =SUMPRODUCT(OFFSET($A$1;myrow-1;mycols-1)) , but a quick check showed that Excel does not process such constructions correctly ...
    – JohnSUN
    Dec 4, 2022 at 6:57
  • The row and column are the results of another formula, they aren't constant. It seems neither INDEX or OFFSET work in the way I want, returning an array given an array of columns.
    – Nicolas
    Dec 4, 2022 at 13:00

0

Reset to default

You must log in to answer this question.