Facing an issue in Sumproduct as shown in the below screenshots.
SS1: As can be seen when i have put the value in Array1, there is no error and a value is getting calculated
SS2 : As soon as i click on Array2, it shows an #Value error in Array 1
Please assist in resolving this issue.
I am not able to put screenshot 1 because new users can put only 1 embedded item hence putting the ss with the error
This is not an issue concerning the view. Therefore an attached image is of little value or of none at all. A tiny example file (example.ods e.g.) showing the issue for you should give the needed information to a helper, and would most likely be returned as exampleReworked.ods containing an exemplified suggestion how to avoid the problem.
To get a promising idea about what actually happens from an image is time-consuming, difficult, and often without success. In addition it causes useless data traffic (340KiByte instead of about 10 KiByte).
Apologies. This is my first post in the community. In other forums, I have found the screenshot to be easier to be understood hence had posted the same since community members may not prefer viewing a file.
I have created a file for the issue and while making the same I think I might understood what is the issue. Will try resolving it prior to posting the issue
You do not need to apologize.
Of course, my comment does not constitute an “official policy”, but only expresses my own thoughts and may be outdated due to age. Anyway, I actually think about such things instead of just doing what everyone does.
On the face of it,
the text box for Array 2 seems empty and the Input Line shows there is nothing after the comma in your formula.
Does the formula work if you enter data manually in text box or use the Shrink button
to select cells from the sheet?
That is not the issue. This is screenshot1 which shows only one array and the formula works fine. As soon as I click on Array 2 to enter data, the #Value error appears in Array 1.
I have also tried interchanging the data in Array 1 and Array 2. As soon as Array 2 is clicked the #Value Error appears.
Please attach something useful.
Well. Let there be an error message for now, don’t pay attention - select the second range of cells and see what happens next. Please be mindful of this warning:
When you clicked on Array2 but haven’t entered a range yet, Calc sees that the sizes of Array1 and Array2 are different and displays an error
Thanks for the reply. I was multiplying two arrays with 10 values. But on reading the corresponding wording, I think the issue is because one array is of 10 row values and the 2nd array is of 10 column values. But here also Calc should take the corresponding values is what I thought would be the system behaviour.
I will trying using the transpose function and see if the issue gets sorted.
I am attaching a sample file with the issue for reference. I am trying to SUMPRODUCT(D2:N2,$B$18:$B$29). In the function window you will notice the #value which appears in Array1.
Thanks
Sumproduct_Issue.ods (23.1 KB)
I’m not sure if only TRANSPOSE() helped to get rid of the error, for sure the addresses of the ranges were also changed. The point is that the size of the range D2:N2 is 11 cells, and $B$18:$B$29 contains 12 cells. In other words, the final formula is either
=SUMPRODUCT(TRANSPOSE($D2:$N2);$B$19:$B$29)
or
=SUMPRODUCT(TRANSPOSE($C2:$N2);$B$18:$B$29)
You are right. While doing the transpose I have also modified the formula =SUMPRODUCT(TRANSPOSE($C2:$N2);$B$18:$B$29)
The Transpose helped resolve the issue
Now my opinionated soul is knocking:
This would have been completely clear based on one single line giving one complete example for the failing formulas. Images and all only hid the relevant facts and caused wasting of time.
Once again: You don’t need to apologize. Nowadays everybody does it this way.
Well, you may now be ready to also consider Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet?.
