Issue 90269

Summary: ODFF: implement COUNTIFS function
Product: Calc Reporter: roadkills_r_us <meo>
Component: programmingAssignee: kla <thomas.klarhoefer>
Status: CLOSED FIXED QA Contact: issues@sc <issues>
Severity: Trivial    
Priority: P3 CC: arielch, gleppert, issues, liushenf, marina.plakalovic, rb.henschel, skundam, vulcain
Version: OOo 3.0 BetaKeywords: ms_interoperability
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: FEATURE Latest Confirmation in: ---
Developer Difficulty: ---
Issue Depends on:    
Issue Blocks: 105140, 117000, 119303    

Description roadkills_r_us 2008-06-02 20:27:26 UTC
Office 2007 apparently has a function in Excel called countifs.  We have
spreadsheets arriving that make use of this function; OO3b works great on most
stuff but barfs when it encounters countifs.  If Oo3 wil not support this we wil
end up having to buy copies of Windows and Office 2007 (in place of using OOo)
because we have a high level manager who can't seem to live without countifs.
Comment 1 Regina Henschel 2008-06-02 21:10:25 UTC
How do your manager can work before Excel 2007? ;)

I leave this issue on "defect" because I cannot find COUNTIFS in
OpenFormula-v1.2-draft9.odt, not even in the "large" group. This might give
problems, when OOo will support this function. This function is new in Excel 2007.

Comment 2 oc 2008-06-04 12:31:02 UTC
Hi Eike, possible for 3.1?
Comment 3 ooo 2008-06-04 15:19:25 UTC
Don't know. We'd first have to define it in the OpenFormula spec now, and
implementation-wise there are certainly more important tasks than this exotic
new Excel function, whose functionality before Excel 2007 usually was
accomplished using SUM with conditions in array context, e.g.
{=SUM((A1:A2>3)*(B4:B5>6))}
Comment 4 roadkills_r_us 2008-06-05 23:31:25 UTC
I have no idea how this manager functioned before Office 2007.  Perhaps he felt
horribly incomplete.

I just know OOo3 was touted as being compatible with Office 2007, and we were
hoping to resolve the manager compatibility problem with this rather than having
to buy copies of Windows and Office for engineers.

Thanks.
Comment 5 ooo 2008-07-11 19:23:53 UTC
For the records: COUNTIFS() was added to the OpenDocument Formula specification,
see http://www.oasis-open.org/committees/documents.php?wg_abbrev=office-formula
Comment 6 colinmercer 2010-01-04 16:33:28 UTC
COUNTIFS is an extremely useful function allowing many different answers in a
single column to be rapidly and accuratele summarised whilst there are multiple
dependances.  The same applies to SUMIFS
Comment 7 erack 2010-01-26 22:56:45 UTC
See also
SUMIFS issue 95144
AVERAGEIFS issue 101466
Comment 8 ooo 2010-08-05 15:27:40 UTC
makkica will be working on this.
Comment 9 skundam 2010-10-20 07:30:49 UTC
Hi All,
    I request you to provide some solution for this Countifs function. If at all 
there is any workaround can anyone help me please?.
Here is the explanation for my issue.
1. I have multiple columns from which i want to select the values which meet 
multiple criterion.
2. Currently I can handle this using countif function (but for single criteria 
only). I want for multiple criteria. Can this be done using any other formula?.

-SATEESH
Comment 10 ooo 2010-10-20 13:29:18 UTC
@skundam:
As said in #desc9 makkica is working on this.

As a workaround you can use SUMPRODUCT, for example to count records that have
the value 1 in the range A1:A4 and the value 2 in the range B1:B4 use
=SUMPRODUCT(A1:A4=1;B1:B4=2)
Comment 11 skundam 2010-10-20 16:01:40 UTC
Hey,
SUMPRODUCT works for me fine. Thanks for the same.

-SATEESH
Comment 12 ooo 2010-11-03 23:21:28 UTC
Note: import/export from/to Excel is not implemented yet.

In cws calcishmakkica:

changeset 02cf226fcde4
http://hg.services.openoffice.org/cws/calcishmakkica/changeset/02cf226fcde4
M formula/inc/formula/compiler.hrc
M formula/inc/formula/opcode.hxx
M formula/source/core/resource/core_resource.src
M formula/source/ui/dlg/parawin.cxx
M sc/inc/sc.hrc
M sc/inc/scfuncs.hrc
M sc/source/core/data/global.cxx
M sc/source/core/inc/interpre.hxx
M sc/source/core/tool/interpr1.cxx
M sc/source/core/tool/interpr4.cxx
M sc/source/core/tool/parclass.cxx
M sc/source/ui/formdlg/dwfunctr.cxx
M sc/source/ui/src/scfuncs.src
M sc/source/ui/unoobj/appluno.cxx
M sc/util/hidother.src

changeset 79da8a1033b6
http://hg.services.openoffice.org/cws/calcishmakkica/changeset/79da8a1033b6
M sc/source/core/inc/parclass.hxx
M sc/source/core/tool/interpr1.cxx
M sc/source/core/tool/parclass.cxx
M sc/source/ui/src/scfuncs.src

You can observe the progress and possible integration date of CWS calcishmakkica at
http://tools.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fcalcishmakkica
Comment 13 ooo 2011-02-17 13:49:54 UTC
Created issue 117000 as a follow-up to implement Excel import/export.
Comment 14 ooo 2011-02-17 16:15:00 UTC
Reassigning to QA for verification.
The latest ODF 1.2 OpenFormula draft is available at
http://www.oasis-open.org/committees/download.php/40744/OpenDocument-v1.2-csd06-rev02-part2.odt
Comment 15 kla 2011-02-22 09:52:55 UTC
Works fine in cws calcishmakkica -> verified
Comment 16 kla 2011-02-28 11:30:09 UTC
seen ok in cws -> verified
Comment 17 Shenfeng Liu 2012-10-11 06:24:33 UTC
Clean up the Target Milestone from AOO 3.5.0, since it is a feature implemented before.
Comment 18 vulcain 2013-01-28 10:19:40 UTC
Resolved in LibreOffice 4.0:
https://bugs.freedesktop.org/show_bug.cgi?id=41214
Comment 19 Ariel Constenla-Haile 2013-01-28 15:02:21 UTC
(In reply to comment #18)

This bug was fixed in OpenOffice with the inclusion off CWS calcishmakkica in 

revision 1381445
revision 1381446
revision 1381447
revision 1381448
revision 1381449
revision 1381450
revision 1381452

After that, LO took the code released under the Apache License.

> Resolved in LibreOffice 4.0:
> https://bugs.freedesktop.org/show_bug.cgi?id=41214

I find it inadmissible that you promote a fork using OpenOffice resources, like this bugzilla