Apache OpenOffice (AOO) Bugzilla – Issue 90269
ODFF: implement COUNTIFS function
Last modified: 2017-05-20 11:42:17 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.
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.
Hi Eike, possible for 3.1?
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))}
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.
For the records: COUNTIFS() was added to the OpenDocument Formula specification, see http://www.oasis-open.org/committees/documents.php?wg_abbrev=office-formula
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
See also SUMIFS issue 95144 AVERAGEIFS issue 101466
makkica will be working on this.
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
@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)
Hey, SUMPRODUCT works for me fine. Thanks for the same. -SATEESH
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
Created issue 117000 as a follow-up to implement Excel import/export.
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
Works fine in cws calcishmakkica -> verified
seen ok in cws -> verified
Clean up the Target Milestone from AOO 3.5.0, since it is a feature implemented before.
Resolved in LibreOffice 4.0: https://bugs.freedesktop.org/show_bug.cgi?id=41214
(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