SUMPRODUCT across multiple sheets

I am trying to get a spreadsheet to perform a SUMPRODUCT across multiple sheets. I kept getting Err:504 in my large spreadsheet, so I decided to make a small sheet to test it with, and no matter what I do, I get an Err:504.

My test spreadsheet has 3 sheets (just called Sheet1-Sheet3) with a number in A1 and A2 on each sheet and then a 4th sheet where I am doing my SUMPRODUCT. I try to do a SUMPRODUCT of the first three sheets like this:

=SUMPRODUCT(Sheet1.A1:Sheet3.A1, Sheet1.A2:Sheet3.A2)

And I get Err:504. But if I try the exact same thing with just a SUM:

=SUM(Sheet1.A1:Sheet3.A1, Sheet1.A2:Sheet3.A2)

It doesn’t give me any error (although, of course this is not what I want). So I assume there isn’t any problem with my syntax.

Can anyone tell me what is going wrong here? I am using:

Version: (x64) / LibreOffice Community
Build ID: 8d71d29d553c0f7dcbfa38fbfda25ee34cce99a2
CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL

Thanks! :beers:

Short answer: nothing wrong, it just doesn’t work like that.

Long answer: SUMPRODUCT() forces its parameters into array mode, arguments are converted to arrays/matrices, but those can be just 2-dimensional, 3D matrices are not possible. SUM() on the other hand takes the cell range reference even if a 3D reference is given and iterates over the range sheet by sheet.

…where “it” means spreadsheet logic as implemented these days, not just LO Calc. Excel will call either of those 3D references a #REF!, which is perhaps a little more friendly than Err:504, but the same idea.

If the cell cursor is positioned on a cell with Err:504 the status bar says “Error in parameter list”.