[Solved] Sumif stopped working

Discuss the spreadsheet application

[Solved] Sumif stopped working

Postby Sootah » Tue Feb 13, 2018 9:40 am

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.
Last edited by robleyd on Sun Mar 11, 2018 2:32 am, edited 1 time in total.
Reason: Tagged [Solved] [robleyd, Moderator]
Open office Version: 4.1.5 Windows 10
Sootah
 
Posts: 5
Joined: Sun Jan 28, 2018 9:28 pm

Re: Sumif stopped working

Postby Lupp » Tue Feb 13, 2018 12:58 pm

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.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1914
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Sumif stopped working

Postby Villeroy » Tue Feb 13, 2018 1:13 pm

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].
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25579
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sumif stopped working

Postby Sootah » Wed Feb 14, 2018 11:45 am

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.
Open office Version: 4.1.5 Windows 10
Sootah
 
Posts: 5
Joined: Sun Jan 28, 2018 9:28 pm

Re: Sumif stopped working

Postby RoryOF » Wed Feb 14, 2018 12:12 pm

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.
Apache OpenOffice 4.1.5 on Xubuntu 18.04 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 26945
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Sumif stopped working

Postby keme » Wed Feb 14, 2018 12:47 pm

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?
User avatar
keme
Volunteer
 
Posts: 2905
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Sumif stopped working

Postby Sootah » Wed Feb 14, 2018 3:20 pm

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.
Open office Version: 4.1.5 Windows 10
Sootah
 
Posts: 5
Joined: Sun Jan 28, 2018 9:28 pm

Re: Sumif stopped working

Postby Villeroy » Wed Feb 14, 2018 4:19 pm

Attach an example document showing the formula and the referenced named ranges.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25579
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sumif stopped working

Postby Sootah » Wed Feb 14, 2018 5:17 pm

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.
Open office Version: 4.1.5 Windows 10
Sootah
 
Posts: 5
Joined: Sun Jan 28, 2018 9:28 pm

Re: Sumif stopped working

Postby Sootah » Sun Mar 11, 2018 2:21 am

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.
Open office Version: 4.1.5 Windows 10
Sootah
 
Posts: 5
Joined: Sun Jan 28, 2018 9:28 pm

Re: [Solved] Sumif stopped working

Postby RusselB » Sun Mar 11, 2018 2:37 am

Happy to hear you found the problem. It can be difficult to spot, as you found out.
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4463
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 20 guests