Ask Your Question
0

SUMPRODUCT + MONTH + IFERROR -> strange behaviour

asked 2017-09-11 15:02:10 +0200

sideshowbond gravatar image

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 =IFERROR(INDEX($Rechnungen.C$1:C$75;MATCH(Einnahmen.A62;$Rechnungen.$A$1:$A$75;0););"") This is column C in 'Einnahmen'.

Then in my assessment sheet I try to reference to that date in =SUMPRODUCT((MONTH($Einnahmen.$C$3:$C$74)=BWA.F$3)($Einnahmen.$G$3:$G$74)($Einnahmen.$K$3:$K$74=BWA.$A4)).

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.

Cheers

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-09-11 18:55:44 +0200

erAck gravatar image

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

edit flag offensive delete link more

Comments

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

sideshowbond gravatar imagesideshowbond ( 2017-09-12 11:19:06 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-11 15:02:10 +0200

Seen: 50 times

Last updated: Sep 11 '17