Search code examples
excelxlsxxls

How to replace a range with list of coordinates in Excel?

This code calculates a sum of string lengths in the column C which are in the same row with a number in column B:

=SUMPRODUCT(ISNUMBER(B2:B1048576)*LEN(C2:C1048576))

enter image description here

If instead of column CI'd like to place a set of coorfinates which describes by the shift indexes in column A how should I change the range to coordinates?

enter image description here

I found that I can convert shift indexes to column letters to get a column coordinate for each row with this code:

=SUBSTITUTE(ADDRESS(1;H1;4);"1";"")

enter image description here

But I don't know how to use this set of coordinates instead of the column range C.

Solution

  • Use INDEX:

    =SUMPRODUCT(LEN(INDEX(C:F,N(IF({1},ROW(B2:B6))),N(IF({1},A2:A6+1))))*ISNUMBER(B2:B6))

    Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

    enter image description here