Bug 41214 - ODFF: implement SUMIFS(), COUNTIFS(), AVERAGEIF() and AVERAGEIFS() spreadsheet functions
Summary: ODFF: implement SUMIFS(), COUNTIFS(), AVERAGEIF() and AVERAGEIFS() spreadshee...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
QA Contact:
Whiteboard: target:4.0.0
Depends on:
Reported: 2011-09-26 02:14 UTC by Lars Andre
Modified: 2013-09-18 21:32 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Lars Andre 2011-09-26 02:14:05 UTC
From the OpenOffice bug report (angeldeath82):
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.

OpenOffice bug report:

Calc/ODFF Implementation/Examine functions:
Comment 1 Björn Michaelsen 2011-12-23 12:35:48 UTC
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Comment 2 GerardF 2012-02-03 01:36:38 UTC
Apache OpenOffice 3.4 will have new Conditional Functions :
"Support conditional functions COUNTIFS, SUMIFS, AVERAGEIF and AVERAGEIFS"

These functions are defined in ODF1.2 : http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part2.html#__RefHeading__1018586_715980110

I think we need this enhancement.
Comment 3 Eike Rathke 2012-05-23 16:31:41 UTC
AOO 3.4 does not have these functions. The pending CWS originating from OOo times was not integrated. The current situation with LibO is waiting for license clearance.
Comment 4 Not Assigned 2012-05-24 03:42:11 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":


resolved fdo#41214 crash due to offset error
Comment 5 Eike Rathke 2012-05-24 03:43:28 UTC
(In reply to comment #4)
bah, wrong bug number in commit, that's for bug 50221 instead.
Comment 6 Winfried Donkers 2012-05-25 02:16:38 UTC
starting on AVERAGEIF, other function are to follow.
Comment 7 Eike Rathke 2012-05-25 02:31:03 UTC
Winfried, please don't, I explained on the mailing list that we should not duplicate this effort, there is a complete and ready solution in the OOo CWS waiting for integration.
Comment 8 Winfried Donkers 2012-05-25 03:04:52 UTC
(In reply to comment #7)
> Winfried, please don't, I explained on the mailing list that we should not
> duplicate this effort, there is a complete and ready solution in the OOo CWS
> waiting for integration.

I must have misunderstood you completely. I will stop my work on AVERAGEIF and put this bug back to NEW (and get back to you on dev-list)
Comment 9 Eike Rathke 2012-12-15 23:07:23 UTC
So, we finally have these in :-)
I pushed a series of commits to master and libreoffice-4-0 branches.