Bug 85985 - SUMIF Function does not evaluate blank cells against criteria.
Summary: SUMIF Function does not evaluate blank cells against criteria.
Status: RESOLVED DUPLICATE of bug 35636
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.3.2 release
Hardware: x86 (IA32) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-11-07 03:59 UTC by klsu
Modified: 2014-11-09 19:09 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description klsu 2014-11-07 03:59:09 UTC
Problem description: SUMIF does not evaluate blank cells in the criteria range to either zero or false or "" (blank), so the only way to sum the values in a range where the corresponding cell in the criteria range is blank is some form of summing the entire range and subtracting the conditional sum of all the values in the range where the corresponding cell in the criteria range is not blank. 

Steps to reproduce:
1. Create 3 columns/ranges, "Units","Bought","Sold" (Number, Date, Date), for example:
 Units |   Bought   |    Sold
    45 | 02/07/1996 | 12/03/1999
    75 | 05/10/2007 | 
    33 | 12/22/2013 |
2. Total number currently owned units should be the total of Units that have no date in Sold (108):
=SUMIF(Sold,"",Units)    gives zero (if A1 is blank =A1=""    returns TRUE )
=SUMIF(Sold,0,Units)     gives zero (if A1 is blank =A1=0     returns TRUE )
=SUMIF(Sold,FALSE,Units) gives zero (if A1 is blank =A1=FALSE returns TRUE )
=SUMIF(Sold,,Units)      (error expected but none returned)

SUMIF appears not to return a value to compare against criteria if a cell being checked is blank. (I have not checked, but wouldn't be surprised if there are other formulas with a similar problem.)
Comment 1 raal 2014-11-09 19:09:19 UTC
Hello,
Thank you for reporting the bug. This issue has previously been reported, so this bug will be resolved as a duplicate of the earlier report. You will automatically be added to the CC list of the other bug.

*** This bug has been marked as a duplicate of bug 35636 ***