[Solved] Sumif stopped working

[Sootah]

Hi. I've had an accounts spreadsheet for several years with sumif formulae referencing another worksheet tab, but suddenly the formulae return zero. The ranges referred to are named ranges which I've checked and are okay. Sumif works if I refer to cells on the same worksheet.
I haven't made any changes that I know of. I opened the file at work in LibreOffice Calc and also in Excel - might this have broken something?
Has there been an update or something?
Thanks for your help.

[Lupp]

1. Opening a Calc document with Excel won't do any harm. Saving it with Excel again may.
2. Your signature shows V1.4.5 of AOO. This means you updated recently. Did the issue already show with your previous version of AOO?
3. Years ago I experienced problems with named ranges in external sheets using LibO. There might be (recently introduced probably) a similar issue with AOO. Make backup copies of your accounts sheet's file and of the other document's file, open the "other" original, and look for all of the used named ranges how they are defined. In your account sheet (original) replace the references using named ranges with the actual (addressing) references. Save the sheets, open your accounts sheet (the one you replaced named references in) again and report your observartions.

[Villeroy]

Make a backup copy of your file.
Remove all confidentials (all text) leaving the SUMIF formula and its referenced numbers and categories.
Upload the document. Click [Post reply] instead of [Quick reply], scroll down and [Upload attachment].

[Sootah]

Thanks for your reply. I have to travel for a couple of weeks but will do this on my return.

The strange thing is that if I set up a completely new worksheet, sumif doesn't work still when referencing a second worksheet. If I use named ranges, it returns zero and if I use cell references, I got some arbitrary number, the source of which I haven't figured out yet.

Sorry to be vague! I was hoping someone might say "ah yes, just uncheck x in settings" or something simple. I'll have to get back on the case when I'm home.

[RoryOF]

The first thing to check when any formula in a spreadsheet doesn't work is the type of the data. Enable /View /Value highlighting and check that your data are all numeric.

[keme]

Are you saying that ranges are correctly accessed inside the sheet, but not from formulas on other sheets in the same file?

AFAIK:
Ranges set up by "automatic header recognition" are local to sheet (tab). Using them from other sheets in the same file yields an error (if the name in use is not present in current sheet) or a "local sum" (which may not be what you need).

Explicitly defined ranges are "global" within the file. You can't have the same explicitly named range on multiple sheets in a file.

Does this apply to your issue?

[Sootah]

Hi. I defined the range by selecting it and typing a name into the cell reference box. It wasn't automatically generated. But also it worked fine until the other day. No error is returned, just zero value.

[Villeroy]

Attach an example document showing the formula and the referenced named ranges.

[Sootah]

Okay will do. But it'll have to wait 2 weeks as I'm not in the country! Sorry. Maybe I should stop the discussion and start again when I'm back.

[Sootah]

Sorry for the delay!!

Okay I figured it. Sorry - false alarm. I'd buggered up the named ranges somehow and mixed the worksheet names up.

Took me weeks to spot it!

Thanks for taking the time to help.

[RusselB]

Happy to hear you found the problem. It can be difficult to spot, as you found out.