I just imported from OpenOffice Calc To LibreOffice and a Fuction is not working

Sphex1411 · January 8, 2025, 5:57pm

I just imported an openoffice spreadsheet that I have used for some time and has been working just fine. In the conversion to LibreOffice. A particular function is giving me a #VALUE! error. The formula is as follows:

=IF(ISBLANK(E67),"",SUMPRODUCT((‘Transfers and Deposits’.$B$89:$AA$94) * (‘Transfers and Deposits’.$B$89:$B$94=E67)))

Basically the formula is to sum the values in B 89 through AA 94 only if it finds E 67 in the column B 89 through B 94.

This runs perfectly fine in OpenOffice with the results I expect but I can not understand why its is not in LibreOffice. Everything else in this large spreadsheet works right but this function and it causes a cascading problem. If anyone can help I would appreciate it. I am making a big assumption that I can use this function this way in LibreOffice. Any help or direction would be appreciated. Thanks is advance !

fpy · January 8, 2025, 7:12pm

.odt ?

and more generally : This is the guide - How to use the Ask site? - #3 by Hrbrgr

Sphex1411 · January 8, 2025, 7:22pm

OpenOffice has the extension .ods

Villeroy · January 8, 2025, 7:31pm

Untitled.ods (12.3 KB)
OpenOffice returns 50.
LibreOffice #VALUE!

Sphex1411 · January 8, 2025, 7:31pm

I am running Both OpenOffice 4.1.15 and Libreoffice 24.8.4.2 on a MAC running MacOS Sequoia 15.2 with a M2 Pro processor .

Sphex1411 · January 9, 2025, 1:53pm

I appreciate you confirming the issue. If there is another way to achieve the same result I would appreciate the help. I do not have anything that comes even close to the simplicity of this formula and function that I can get to work. Thanks

Lupp · January 9, 2025, 2:15pm

If your “you” is @Villeroy I would like to tell that column B contains strings, and SUMPRODUCT doesn’t ignore them as SUM() would do. (That’s not the complete problem. The bad products of texts with logical 1 or 0 should throw an error anyway.)
Therefore the #VALUE! error is to be expected in his sample sheet.
AOO Calc simply isn’t conformant with OASIS odf specifications insofar.

=SUMPRODUCT(('Transfers and Deposits'.$C$89:$AA$94) * ('Transfers and Deposits'.$B$89:$B$94=E67))

Would work for @Villeroy’s example in AOO and in LibO as well.

See attached example:
disask116358_AOO_missingPropagateError.ods (13.7 KB)