SUMPRODUCT + MONTH + IFERROR -> strange behaviour

Hello hive mind,

Im running my accounts in an elaborate calc file and as of LO 5.2 (through to 5.4) I got a weird issue (hence I am still running 5.1):

I got a sheet for paid invoices (Einnahmen) which is pulling the date from another sheet (Rechnungen) with
This is column C in ‘Einnahmen’.

Then in my assessment sheet I try to reference to that date in

This should give me an monthly overview which part of my business has generated how much income.

Up until 5.1 that was working fine. From 5.2 onwards I get #VALUE! as error. The cell formatting seems to be fine and for my expenses (same sheet) it works fine (though that doesn’t use referenced dates).

SUMPRODUCT without the MONTH part works so I am led to believe that its somewhere in the referenced date that things go wonky however MONTH on its own gives a valid value of the referenced date - I am stumped.

Any help at this point is much appreciated.


Without seeing the document it’s near to impossible to help. A common cause for #VALUE! errors is that some data is in string form where a numeric value is expected. If and how or whether not those are converted depends on the settings under Tools → Options → Calc → Formula, “Detailed Calculation Settings”, under Custom “Contents to Numbers” several strategies can be chosen, of which “Generate #VALUE! error” is the best as it detects possible errors earliest, but may lead to such errors with existing erroneous data. See the corresponding help entry. YMMV…

I am aware that #VALUE! can be hard to track down. The one thing confusing the heck out of me is that it used to work perfectly fine in 5.1 but in subsequent versions it was showing unexpected behaviour. Hence I was wondering whether someone might have come across something similar in the upgrading process and might have a clue in what direction to go.
Thanks for your advice with Calculation Settings, that didn’t change anything though…