Bug 85985 - SUMIF Function does not evaluate blank cells against criteria.
SUMIF Function does not evaluate blank cells against criteria.
 Status: RESOLVED DUPLICATE of bug 35636 None LibreOffice Unclassified Calc (show other bugs) 4.1.3.2 release x86 (IA32) Linux (All) medium normal Not Assigned

 Reported: 2014-11-07 03:59 UTC by klsu 2014-11-09 19:09 UTC (History) 1 user (show) raal

Attachments

 Note You need to log in before you can comment on or make changes to this bug.
 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.)``` 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 ***```