Issue 84168 - DCOUNT(..;0;..) counts text cells
Summary: DCOUNT(..;0;..) counts text cells
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOo 2.3
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2007-12-02 13:10 UTC by drking
Modified: 2013-02-07 22:01 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description drking 2007-12-02 13:10:57 UTC
DCOUNT should only count cells with numbers, same as COUNT

  A   B
1 pet name
2 dog fido
3 cat meow
4 dog rover
5
6 pet name
7 dog

DCOUNT(A1:B4;0;A6:B7) returns 1, with not a number in sight

although
DCOUNT(A1:B4;2;A6:B7) returns 0, correctly

The use of 0 here is poorly explained in the Help (I'm re-writing it 
currently), and it is not compatible with Excel, at least with older versions. 
But in any event it should only count numbers.
Comment 1 kpalagin 2008-01-10 11:58:45 UTC
Confirming with 2.4m241 on WinXP - if second parameter (database_field) is 
zero for DCOUNT, we apparently count non-digits. Excel2003 in such case 
produces #VALUE.
Comment 2 drking 2008-01-10 12:55:08 UTC
Excel2003 probably produces #VALUE because it doesn't understand 0 as a 
parameter?

When someone looks at this, it would be useful to know exactly what field=0 is 
*supposed* to do.. does it count any row which has a number in any column, or 
any cell containing a number, or ...

many thanks

Comment 3 Regina Henschel 2009-08-02 00:24:30 UTC
The index 0 is an old syntax from StarOffice 5.2 and has the same meaning as
using the whole data table range as second parameter. In this cases StarOffice
5.2 applies the aggregate function to the right most column of the data table
for DCOUNT and for DCOUNTA. In StarOffice 5.2 you will get the correct result 0
for your example.

The results in OOo2 and OOo3 for the value 0 for the "Field" parameter differ
from those in StarOffice 5.2 and are wrong, not only for the new file format but
also when opening an old sdc file. I have no version OOo 1 installed to look there.

The ODF 1.2 draft spec does not mention this syntax at all, but only uses the
singular "Field". In addition I find "A field selector that selects a
non-existent field is an error." in chapter 4.9.7. This would be true for the
value 0, because the leftmost column has index 1.

Therefore I think, that OOo should return an error for the old syntax, when it
is used in an ODF format and use the old implementation of "apply aggregate
function to rightmost column" with the correct meaning of DCOUNT and DCOUNTA
concerning number and text, when it is used in an old, non ODF format.

The help is very unclear in this point and needs to be improved, not only
concerning value 0 of the second parameter. For example the text "Indicates
which database field (column) is to be used for the search criteria." for the
second parameter is nonsense. The second parameter determines the field on which
the aggregate function is applied. Which fields are used for the search criteria
is part of the search criteria range itself.
Comment 4 drking 2009-08-02 07:04:18 UTC
Thanks, Regina.

I wrote some improved help on the Wiki back in 07:

http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_DCOUNT_function

It links back to this issue.