Bug 58689 - Countifs formula does not count blanks
Summary: Countifs formula does not count blanks
Status: CLOSED INVALID
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.0.beta2
Hardware: PowerPC Windows (All)
: high critical
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-12-23 18:59 UTC by Crusader
Modified: 2013-11-16 23:02 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet showing failure of Countifs to count blanks (9.92 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-12-23 18:59 UTC, Crusader
Details
Spreadsheet showing failure of Countifs to count blanks (9.92 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-12-23 20:08 UTC, Crusader
Details
Spreadsheet text showing failure of Countifs to count blanks (419 bytes, text/plain)
2012-12-25 01:31 UTC, Crusader
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Crusader 2012-12-23 18:59:08 UTC
Created attachment 72040 [details]
Spreadsheet showing failure of Countifs to count blanks

The following formula does not count blanks:

=COUNTIFS(C1:C10,"Good",B1:B10,"Dairy",D1:D10,"")

This formula returns a value of zero.  The correct value should be 1 (one).

This formula was tested in Excel and returned the correct value of one.
Comment 1 Crusader 2012-12-23 19:57:42 UTC
Sumifs also does not recognize blanks
Comment 2 Crusader 2012-12-23 20:02:40 UTC
I am not familiar with bug reporting.  I uploaded my Calc spreadsheet (in .ods format); however, when I reviewed it on the bug reporting site, it states that the upload was text (and is unreadable).  If I can get directions on how to upload the LO Calc spreadsheet, I will be happy to upload it again.
Comment 3 Crusader 2012-12-23 20:08:09 UTC
Created attachment 72043 [details]
Spreadsheet showing failure of Countifs to count blanks
Comment 4 Crusader 2012-12-25 01:31:44 UTC
Created attachment 72090 [details]
Spreadsheet text showing failure of Countifs to count blanks

Yet another attempt to upload a sample spreadsheet to support my report.
Comment 5 Cor Nouws 2013-01-05 17:40:42 UTC
Hi crusader,

(In reply to comment #0)
> =COUNTIFS(C1:C10,"Good",B1:B10,"Dairy",D1:D10,"")

Your formula is wrong. Should be something like
  =COUNTIF(C1:C10;"GOOD")+COUNTIF(C1:C10;"FOO")

Apart from that, I'n not aware that blanks (empty strings) should be counted.
I expect the user support lists and such, can give you proper advise for that?
Thanks,
Comment 6 Crusader 2013-01-05 22:39:51 UTC
(In reply to comment #5)

Thank you for the feedback,  Cor Nouws.

SUMPRODUCT works perfectly (counts blanks): =SUMPRODUCT(C1:C10="Good",B1:B10="Dairy",D1:D10="")

I thought COUNTIFS may do the same...

> Hi crusader,
> 
> (In reply to comment #0)
> > =COUNTIFS(C1:C10,"Good",B1:B10,"Dairy",D1:D10,"")
> 
> Your formula is wrong. Should be something like
>   =COUNTIF(C1:C10;"GOOD")+COUNTIF(C1:C10;"FOO")
> 
> Apart from that, I'n not aware that blanks (empty strings) should be counted.
> I expect the user support lists and such, can give you proper advise for
> that?
> Thanks,
Comment 7 Cor Nouws 2013-01-06 09:22:41 UTC
Hi crusader,
Thanks for confirming the better way to do that job ;-)
Resolving this issue then
Comment 8 Cor Nouws 2013-01-06 09:22:58 UTC
& close