Apache OpenOffice (AOO) Bugzilla – Issue 95144
ODFF: implement SUMIFS()
Last modified: 2017-05-20 11:43:17 UTC
With Microsoft Excel 2007 they added a function (SUMIFS) which functions as follows: SUMIFS(SumRange,SearchRange,Criteria). They added this to deal with unsorted values in SearchRange, as their implementation of SUMIF is based on SearchRange being sorted.
Are you sure? I think SUMIF not need a sorted range.
The implementation in OpenOffice 3.0.0m9's Calc does not require a sorted SearchRange, however the implementation in Microsoft Excel does require a sorted SearchRange.
Both, the assumption that in Excel SUMIF(SearchRange,Criteria,SumRange) would need a sorted SearchRange and a SUMIFS(SumRange,SearchRange,Criteria) function would had been added that would SearchRange not need to be sorted are not correct. Correct is, that SUMIF() does not need the SearchRange to be sorted. Correct is, that Excel 2007 added a function SUMIFS(SumRange,CriteriaRange1,Criteria1,CriteriaRange2,Criteria2,...) that sums a range if it matches multiple criteria. Correct is also, that SUMIFS() should be specified in ODFF, and implemented in OOo.
*** Issue 101267 has been marked as a duplicate of this issue. ***
Created attachment 61823 [details] some examples of the error
Created attachment 61824 [details] patch to exchange minus with approxSub; only 1 line
Please excuse the mess up. The attached files belong to issue 101316. I fall into the "next issue in your list"-trap.
I have a document in Excel 2007 which has a lot of SUMIFS. Is there a way around this not working by restating the formula in a way which Calc understands? Example: =sumifs($H$4:$H$506;$A$4:$A$506;">=11/1/09";$A$4:$A$506;"<=11/30/09") Thank you
@tela Workaround =SUMPRODUCT($H$4:$H$506;($A$4:$A$506>=DATEVALUE("11/1/09"))*($A$4:$A$506<=DATEVALUE("11/30/09"))) Please ask such questions in a forum or mailing list next time.
See also COUNTIFS issue 90269 AVERAGEIFS issue 101466
makkica will be working on this.
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
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.