[Solved] SUMIF() formula stopped working

[CalcOfficer]

Hi,

I have used the following formula before but it stopped working probably after some open office update.
I use Open Office Version 4.1.2 on Windows 10.

Code: Select all

=SUMIF(Tilikausi2018.$B$3:$B$524;">=1.1.2018";Tilikausi2018.$C$3:$C$524)-SUMIF(Tilikausi2018.$B$3:$B$524;">=1.2.2018";Tilikausi2018.$C$3:$C$524)

The calculation does not do the minus (-) part of the formula and results 0.

If I remove the

Code: Select all

-SUMIF(Tilikausi2018.$B$3:$B$524;">=1.2.2018";Tilikausi2018.$C$3:$C$524)

it calculates

/CalcOfficer

[Zizi64]

Please upload your ODF type sample file here.

[CalcOfficer]

I think I found a reason. The formula is somehow locked and if do a dummy change there it starts to calculate correctly.
I have automatic calculation on and recalculation doesn't help.

[CalcOfficer]

I solved the problem by replacing one character in each formula
by using FIND/REPLACE "replace all". This was a workaround and the root cause is still unclear
why those formulas were locked.

[RusselB]

As Tibor asked, please upload a sample file that has the problem showing.
It's impossible to diagnose what is happening with those formulas without seeing the actual spreadsheet.

[CalcOfficer]

I'm sorry I can not do that for the following reasons:
- the file structure is pretty complicated with plenty of sheets and the formulas are pointing to different sheets
- there is also sensitive information I don't want to share
- the problem does not exist any more, so how to troubleshoot?

The main reason for the issue was that the formulas had become somehow passive (locked) and they
"woke up" after you made a dummy edit operation there. It was not depending on the calculation since the calculation option was all the time on.
Furthermore recalculation did not help either.

I remember one case some time ago when the macros of this file stopped working and I had to reactivate them.
The issue with formulas might be related to that case?

Thank you for your replies

[Zizi64]

I remember one case some time ago when the macros of this file stopped working and I had to reactivate them.
The issue with formulas might be related to that case?

How we can guess it without a sample file (without the full macro code, without the working formulas...)?

Why they sopped working?
How you reactivated them?

[CalcOfficer]

How we can guess it without a sample file (without the full macro code, without the working formulas...)?

I understand this fully, anyway from my point of view there is no need for further troubleshooting.

Why they sopped working?
How you reactivated them?

If I remember correctly it was related to the file security that was changed for some reason (not by me) to status "high".
After I changed the status to low the macros started to work again.

[Zizi64]

If I remember correctly it was related to the file security that was changed for some reason (not by me) to status "high".
After I changed the status to low the macros started to work again.

It is enough to set the macro security to "Medium". The software will warn you that the file contains some macros, and you can enable or disable them in the actual file by one clicking.

[CalcOfficer]

It is enough to set the macro security to "Medium". The software will warn you that the file contains some macros, and you can enable or disable them in the actual file by one clicking.

Ok, thanks.
By selecting "Low" you can avoid the warning? I think it works in the same way in MS Excel?

[Zizi64]

By selecting "Low" you can avoid the warning? I think it works in the same way in MS Excel?

I do not know how the Excel working. There is not any warning when you set the level to "low" in AOO/LO. (It is dangerous a littlebit...)

[robleyd]

From the Help F1 - search macro security levels:

Medium
Confirmation required before executing macros from unknown sources.
Trusted sources can be set on the Trusted Sources tab page. Signed macros from a trusted source are allowed to run. In addition, any macro from a trusted file location is allowed to run. All other macros require your confirmation.
Low (not recommended)
All macros will be executed without confirmation. Use this setting only if you are certain that all documents that will be opened are safe.
A macro can be set to auto-start, and it can perform potentially damaging actions, as for example delete or rename files. This setting is not recommended when you open documents from other authors.