Issue 90269 - ODFF: implement COUNTIFS function
Summary: ODFF: implement COUNTIFS function
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOo 3.0 Beta
Hardware: All All
: P3 Trivial with 6 votes (vote)
Target Milestone: ---
Assignee: kla
QA Contact: issues@sc
Keywords: ms_interoperability
Depends on:
Blocks: 105140 117000 119303
  Show dependency tree
Reported: 2008-06-02 20:27 UTC by roadkills_r_us
Modified: 2017-05-20 11:42 UTC (History)
8 users (show)

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


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

Comment 5 ooo 2008-07-11 19:23:53 UTC
For the records: COUNTIFS() was added to the OpenDocument Formula specification,
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?.

Comment 10 ooo 2010-10-20 13:29:18 UTC
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
Comment 11 skundam 2010-10-20 16:01:40 UTC
SUMPRODUCT works for me fine. Thanks for the same.

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
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
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
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
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:
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:

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