Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 19 Apr 2020 13:17:01 +0200Sumproduct() of a specified row of an array.https://ask.libreoffice.org/en/question/239262/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:
=SUMPRODUCT(INDEX($J$9:$BM17,$BQ1),$J27:$BM27)
but that produces an error message saying "wrong data type". How can I do this?Sat, 18 Apr 2020 20:52:18 +0200https://ask.libreoffice.org/en/question/239262/sumproduct-of-a-specified-row-of-an-array/Comment by keme for <p>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: </p>
<p>=SUMPRODUCT(INDEX($J$9:$BM17,$BQ1),$J27:$BM27)</p>
<p>but that produces an error message saying "wrong data type". How can I do this?</p>
https://ask.libreoffice.org/en/question/239262/sumproduct-of-a-specified-row-of-an-array/?comment=239287#post-id-239287If 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.Sun, 19 Apr 2020 00:21:32 +0200https://ask.libreoffice.org/en/question/239262/sumproduct-of-a-specified-row-of-an-array/?comment=239287#post-id-239287Comment by uglybug for <p>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: </p>
<p>=SUMPRODUCT(INDEX($J$9:$BM17,$BQ1),$J27:$BM27)</p>
<p>but that produces an error message saying "wrong data type". How can I do this?</p>
https://ask.libreoffice.org/en/question/239262/sumproduct-of-a-specified-row-of-an-array/?comment=239278#post-id-239278hi James,
check that there are no string characters within any of the selected cells
also the cell format should be numeric not alpha-numericSat, 18 Apr 2020 22:41:27 +0200https://ask.libreoffice.org/en/question/239262/sumproduct-of-a-specified-row-of-an-array/?comment=239278#post-id-239278Answer by Steph for <p>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: </p>
<p>=SUMPRODUCT(INDEX($J$9:$BM17,$BQ1),$J27:$BM27)</p>
<p>but that produces an error message saying "wrong data type". How can I do this?</p>
https://ask.libreoffice.org/en/question/239262/sumproduct-of-a-specified-row-of-an-array/?answer=239353#post-id-239353I have built a spreadsheet reproducing your layout and it is working fine for me. I am using Calc 6.4.2.2. under Windows10.
[C:\fakepath\Sumproduct.ods](/upfiles/15872949493507741.ods)Sun, 19 Apr 2020 13:17:01 +0200https://ask.libreoffice.org/en/question/239262/sumproduct-of-a-specified-row-of-an-array/?answer=239353#post-id-239353