Sumproduct() of a specified row of an array

j9:bm17 contains an array, and $BQ1 contains the number of a row in that array. I want to compute the sumproduct of that row with j24:BM27. My best guess so far was:


but that produces an error message saying “wrong data type”. How can I do this?

hi James,
check that there are no string characters within any of the selected cells
also the cell format should be numeric not alpha-numeric

If the tip from uglybug does not lead to a solution, check also that the value in BQ1 is numeric, and in the range 1-9. If you select that cell and it shows a leading apostrophe in the formula field, the number is entered as text.

If it is a formula, perhaps share that formula with us.

I have built a spreadsheet reproducing your layout and it is working fine for me. I am using Calc under Windows10.