Welcome to NeoOffice developer notes and announcements
NeoOffice
Developer notes and announcements
 
 

This website is an archive and is no longer active
NeoOffice announcements have moved to the NeoOffice News website


Support
· Forums
· NeoOffice Support
· NeoWiki


Announcements
· Twitter @NeoOffice


Downloads
· Download NeoOffice


  
NeoOffice :: View topic - SUMIF malfunction if criterion is null cell value?
SUMIF malfunction if criterion is null cell value?
 
   NeoOffice Forum Index -> NeoOffice Releases
View previous topic :: View next topic  
Author Message
stuckfly
Blue Pill


Joined: Mar 20, 2013
Posts: 1

PostPosted: Wed Mar 20, 2013 6:03 pm    Post subject: SUMIF malfunction if criterion is null cell value?

Whew, I guess I got in under the wire for direct support! I got your email warning of cutting off new support customers yesterday, and I found a problem I need help with today. So, I'm a paying supporter of NeoOffice:)

I'm coming from Excel, and I am used to using empty cells as criteria for formulae, using the criterion [cell]="". This works for some cases in NeoOffice, for example: =if(B4="";"true";"false") yields "true" if the cell value is null.

However, the SUMIF function does not seem to like it. If range Reconciled is a column of "x" or "" (null) and I use =SUMIF(B1:B100;"";D1:D100) I get the sum of ALL values in D1:D100 even though many cells in B1:B100 contain empty cells (also tried deleting all values in B1:B100). Testing the same cells for valid "" using the IF formula above produces "true" for the empty ones; else "false".

It works in Excel. Is this a bug in NeoOffice? Is there another way?

I'm really under the gun to fix this, because my business contract requires me to build the spreadsheet for which I am needing this method, and I just failed an audit for the requirement. I have 30 days to get it done, so please respond ASAP (even if your answer won't solve the problem). Sorry, I know you guys are busy!
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Wed Mar 20, 2013 6:37 pm    Post subject: Re: SUMIF malfunction if criterion is null cell value?

stuckfly wrote:
It works in Excel. Is this a bug in NeoOffice? Is there another way?


NeoOffice is not Excel. It is based on OpenOffice.org so if you will find differences in similarly named functions in NeoOffice than in Excel.

stuckfly wrote:
I'm really under the gun to fix this, because my business contract requires me to build the spreadsheet for which I am needing this method, and I just failed an audit for the requirement. I have 30 days to get it done, so please respond ASAP (even if your answer won't solve the problem). Sorry, I know you guys are busy!


Unlike many other sites, these forums are not a "user to user" or "customer service" forum. Instead, this forum is generally used by users to post critical crashing or hanging bugs. In return for the US$100 payment, we focus all our attention immediately on finding and fixing such critical bugs.

Since the fact that NeoOffice's underlying OpenOffice.org code's SUMIF function being different than Excel's does not fall within that scope, I will be happy to refund your payment.

Patrick
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Wed Mar 20, 2013 7:09 pm    Post subject:

BTW, in NeoOffice and OpenOffice.org, testing for "" only tests for a zero length string. The ISBLANK() function tests for null or emptiness.

Patrick
Back to top
amayze
The Merovingian


Joined: Oct 24, 2005
Posts: 561
Location: Edinburgh, Scotland

PostPosted: Fri Mar 22, 2013 2:45 am    Post subject:

Strangely when I use "" in a SUMIF function it always sums to 0.

The problem with ISBLANK() is that it requires a cell reference and therefore cannot be used in a SUMIF function. Therefore you need to add a column to your spreadsheet that determines the blankness of the reference cell and use that column in your SUMIF function.

See attached screenshots.

Hope that helps.

Andy
Back to top
Display posts from previous:   
   NeoOffice Forum Index -> NeoOffice Releases All times are GMT - 7 Hours
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum

Powered by phpBB © 2001, 2005 phpBB Group

All logos and trademarks in this site are property of their respective owner. The comments are property of their posters, all the rest © Planamesa Inc.
NeoOffice is a registered trademark of Planamesa Inc. and may not be used without permission.
PHP-Nuke Copyright © 2005 by Francisco Burzi. This is free software, and you may redistribute it under the GPL. PHP-Nuke comes with absolutely no warranty, for details, see the license.