SUMPRODUCT across multiple sheets

[drsudo]

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: 7.2.7.2 (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!

[erAck]

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.

[joshua4]

@erAck

it just doesn’t work like that

…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.

[erAck]

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