Copy formula not working

[Pheeble]

In Calc 5.3.2.2 (Xubuntu 16.04), I have a spreadsheet containing two sheets. One of the sheets contains a row of cells containing a formula with absolute references that displays the totals for a given month extracted from the other sheet.

The formula to get the totals for March (ie. month = 3) is: {=SUM(IF(MONTH(2017_Itemised.H3:2017_Itemised.H1048576)=3,2017_Itemised.I3:2017_Itemised.I1048576,0))}

I realised that the total for April was displaying 0.0 despite valid values for April being in the ‘2017_Itemised’ sheet.

I tried copying and pasting the formula from the formula bar for ‘March’ into ‘April’, but in selecting the formula bar the brace characters { and } disappear, so the resulting formula is: =SUM(IF(MONTH(2017_Itemised.H3:2017_Itemised.H1048576)=4,2017_Itemised.I3:2017_Itemised.I1048576,0))

I then tried manually editing the formula in the formula bar to insert the braces, but the cell then just displays the formula string as text without calculating anything.

I have no idea what is going on here. The formula works for every other cell except this one, but as far as I can see the cells are identically configured in every way. Is there some special way to copy such a formula to another cell to make it work?

[Pheeble]

Answering my own question: I got the formula to work by using ‘Function Wizard’ to insert the formula and ticking the ‘Array’ checkbox.

Eventually I realised that, after copying and pasting the array formula, I should have pressed ‘Ctrl+Shift+Enter’ instead of just ‘Enter’. Talk about obscure input requirements. I don’t see why ‘Enter’ wouldn’t be sufficient.

[erAck]

Because in array/matrix mode formulas calculate differently than in “normal” mode. As you have encountered. The difference is that for functions that expect a single scalar value as argument (here for example MONTH and IF) in normal mode the implicit intersection of the formula cell position and the range reference given is passed as argument, while in array mode the function iterates over every element of the range reference and the result is an array instead of a single value.