Help with formula that worked in Excel, but not in LibreOffice Calc

chickapinfalls · September 2, 2019, 3:59pm

I have the following formula that works in Excel and WPS Office, but not in LibreOffice Calc. I get Error: 504 on this. Any help would be greatly appreciated as I really want to move away from Excel.

=INDEX(SUMPRODUCT((((C:C>=DATE(2019,6,6))*(C:C<=DATE(2019,7,5)))*(E:E="X")),H:H),1)

Thank you,

Chuck

mikekaganski · September 2, 2019, 4:08pm

Do you call INDEX like INDEX(<number>;1)? So what should it return?

By the way, the SUMPRODUCT would look much simpler like this:

SUMPRODUCT(C:C>=DATE(2019;6;6);C:C<=DATE(2019;7;5);E:E="X";H:H)

erAck · September 2, 2019, 4:23pm

I just indented the question’s formula expression by 4 spaces to actually format it as code so the two * asterisk multiplication operators aren’t swallowed as inline italic formatting…

mikekaganski · September 2, 2019, 4:26pm

But yes, Excel 2016 actually does allow INDEX to be called like that; and it obviously simply treats a number in its first argument as a 1-element array… Then - your formula may be simplified to what I wrote in the first comment.

chickapinfalls · September 2, 2019, 5:25pm

Thank you Mike. Your re-write worked perfectly! Thanks again.

erAck · September 2, 2019, 4:36pm

SUMPRODUCT() returns exactly one scalar value, applying INDEX(…;1) on that is superfluous. Might be that Excel accepts it as a special case of a 1x1 array, but it doesn’t make sense other than to return the one value if the row argument is 1 (or even omitted).