Issue 95144

Summary: ODFF: implement SUMIFS()
Product: Calc Reporter: angeldeath82 <julius_castus>
Component: programmingAssignee: kla <thomas.klarhoefer>
Status: CLOSED FIXED QA Contact: issues@sc <issues>
Severity: Trivial    
Priority: P3 CC: arielch, issues, liushenf, marina.plakalovic, rb.henschel, vulcain
Version: OOO300m9Keywords: ms_interoperability
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: FEATURE Latest Confirmation in: ---
Developer Difficulty: ---
Issue Depends on:    
Issue Blocks: 105140, 117000, 119303    
Attachments:
Description Flags
some examples of the error
none
patch to exchange minus with approxSub; only 1 line none

Description angeldeath82 2008-10-19 04:05:52 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.
Comment 1 mariosv 2008-10-19 17:49:17 UTC
Are you sure?
I think SUMIF not need a sorted range.
Comment 2 angeldeath82 2008-10-20 03:12:02 UTC
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.
Comment 3 ooo 2008-10-23 15:50:58 UTC
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.
Comment 4 Regina Henschel 2009-04-22 15:18:20 UTC
*** Issue 101267 has been marked as a duplicate of this issue. ***
Comment 5 frank 2009-04-25 11:47:39 UTC
*** Issue 101267 has been marked as a duplicate of this issue. ***
Comment 6 Regina Henschel 2009-04-26 22:21:37 UTC
Created attachment 61823 [details]
some examples of the error
Comment 7 Regina Henschel 2009-04-26 22:23:13 UTC
Created attachment 61824 [details]
patch to exchange minus with approxSub; only 1 line
Comment 8 Regina Henschel 2009-04-26 22:31:47 UTC
Please excuse the mess up. The attached files belong to issue 101316. I fall
into the "next issue in your list"-trap.
Comment 9 tela 2009-11-07 18:32:37 UTC
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
Comment 10 Regina Henschel 2009-11-08 14:58:02 UTC
@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.
Comment 11 erack 2010-01-26 22:56:59 UTC
See also
COUNTIFS issue 90269
AVERAGEIFS issue 101466
Comment 12 ooo 2010-08-05 15:28:39 UTC
makkica will be working on this.
Comment 13 ooo 2010-11-03 23:21:56 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 14 ooo 2011-02-17 13:53:48 UTC
Created issue 117000 as a follow-up to implement Excel import/export.
Comment 15 ooo 2011-02-17 16:15:18 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 16 kla 2011-02-28 13:00:37 UTC
seen ok in cws -> verified
Comment 17 Shenfeng Liu 2012-10-11 05:54:28 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:18:55 UTC
Resolved in LibreOffice 4.0:
https://bugs.freedesktop.org/show_bug.cgi?id=41214
Comment 19 Ariel Constenla-Haile 2013-01-28 15:07:00 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.