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:
=SUMPRODUCT(INDEX($J$9:$BM17,$BQ1),$J27:$BM27)
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 6.4.2.2. under Windows10.