calcishmakkica: #i90269# #i95144# #i101466# implement SUMIFS, AVERAGEIFS, COUNTIFS

Merged from Apache OO with adaptions.
http://svn.apache.org/viewvc?rev=1381447&view=rev
Original Apache OO committer: Andrew Rist <arist@apache.org>

Original Author: Marina Plakalovic <makkica@openoffice.org>
Original Committer: Eike Rathke [er] <eike.rathke@oracle.com>

# HG changeset patch
# User Eike Rathke [er] <eike.rathke@oracle.com>
# Date 1288810126 -3600
# Node ID 02cf226fcde498f6fd926d45df497e9fb412fe0f
# Parent  528da6bfd0daed4355d745590d5ac3a319b08fb4

Change-Id: I08754653cd2ff20536ad3e9f260f747cb127ccdd

12 files changed, 599 insertions, 12 deletions

diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h
index 6615183240eb..2e2aa4c6c9e3 100644
--- a/sc/inc/helpids.h
+++ b/sc/inc/helpids.h
@@ -446,6 +446,9 @@
 #define HID_FUNC_PRODUKT                                        "SC_HID_FUNC_PRODUKT"
 #define HID_FUNC_SUMMEWENN                                      "SC_HID_FUNC_SUMMEWENN"
 #define HID_FUNC_AVERAGEIF                                      "SC_HID_FUNC_AVERAGEIF"
+#define HID_FUNC_SUMIFS                                         "SC_HID_FUNC_SUMIFS"
+#define HID_FUNC_AVERAGEIFS                                     "SC_HID_FUNC_AVERAGEIFS"
+#define HID_FUNC_COUNTIFS                                       "SC_HID_FUNC_COUNTIFS"
 #define HID_FUNC_ZAEHLENWENN                                    "SC_HID_FUNC_ZAEHLENWENN"
 #define HID_FUNC_WURZEL                                         "SC_HID_FUNC_WURZEL"
 #define HID_FUNC_ZUFALLSZAHL                                    "SC_HID_FUNC_ZUFALLSZAHL"
diff --git a/sc/inc/sc.hrc b/sc/inc/sc.hrc
index 2849c9fd53e9..9eaf61ffde1f 100644
--- a/sc/inc/sc.hrc
+++ b/sc/inc/sc.hrc
@@ -28,6 +28,7 @@
 #include "helpids.h"
 
 #define VAR_ARGS                30 // variable Parameter in scfuncs.src
+#define PAIRED_VAR_ARGS			(VAR_ARGS + VAR_ARGS)
 
 // areas
 
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index aed60e770bdc..55b8b9c94405 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -3972,6 +3972,7 @@ void Test::testFunctionLists()
         "ATAN2",
         "ATANH",
         "AVERAGEIF",
+        "AVERAGEIFS",
         "BITAND",
         "BITLSHIFT",
         "BITOR",
@@ -3987,6 +3988,7 @@ void Test::testFunctionLists()
         "COTH",
         "COUNTBLANK",
         "COUNTIF",
+        "COUNTIFS",
         "CSC",
         "CSCH",
         "DEGREES",
@@ -4022,6 +4024,7 @@ void Test::testFunctionLists()
         "SUBTOTAL",
         "SUM",
         "SUMIF",
+        "SUMIFS",
         "SUMSQ",
         "TAN",
         "TANH",
diff --git a/sc/source/core/data/funcdesc.cxx b/sc/source/core/data/funcdesc.cxx
index e34dd2098f54..32500eebdd29 100644
--- a/sc/source/core/data/funcdesc.cxx
+++ b/sc/source/core/data/funcdesc.cxx
@@ -84,7 +84,10 @@ ScFuncDesc::~ScFuncDesc()
 void ScFuncDesc::Clear()
 {
     sal_uInt16 nArgs = nArgCount;
-    if (nArgs >= VAR_ARGS) nArgs -= VAR_ARGS-1;
+    if (nArgs >= PAIRED_VAR_ARGS)
+        nArgs -= PAIRED_VAR_ARGS - 2;
+    else if (nArgs >= VAR_ARGS)
+        nArgs -= VAR_ARGS - 1;
     if (nArgs)
     {
         for (sal_uInt16 i=0; i<nArgs; i++ )
@@ -147,7 +150,7 @@ void ScFuncDesc::Clear()
                     aSig.getLength() >= 2)
                 aSig.setLength(aSig.getLength() - 2);
         }
-        else
+        else if ( nArgCount < PAIRED_VAR_ARGS)
         {
             sal_uInt16 nFix = nArgCount - VAR_ARGS;
             for ( sal_uInt16 nArg = 0; nArg < nFix; nArg++ )
@@ -172,6 +175,34 @@ void ScFuncDesc::Clear()
             aSig.append(sep);
             aSig.appendAscii(" ... ");
         }
+        else
+        {
+            sal_uInt16 nFix = nArgCount - PAIRED_VAR_ARGS;
+            for ( sal_uInt16 nArg = 0; nArg < nFix; nArg++ )
+            {
+                if (!pDefArgFlags[nArg].bSuppress)
+                {
+                    aSig.append(*(ppDefArgNames[nArg]));
+                    aSig.append(sep);
+                    aSig.appendAscii( " " );
+                }
+            }
+
+            aSig.append(*(ppDefArgNames[nFix]));
+            aSig.append(sal_Unicode('1'));
+            aSig.appendAscii( ", " );
+            aSig.append(*(ppDefArgNames[nFix+1]));
+            aSig.append(sal_Unicode('1'));
+            aSig.append(sep);
+            aSig.appendAscii( " " );
+            aSig.append(*(ppDefArgNames[nFix]));
+            aSig.append(sal_Unicode('2'));
+            aSig.appendAscii( ", " );
+            aSig.append(*(ppDefArgNames[nFix+1]));
+            aSig.append(sal_Unicode('2'));
+            aSig.append(sep);
+            aSig.appendAscii( " ... " );
+        }
     }
 
     return aSig.makeStringAndClear();
@@ -243,7 +274,9 @@ sal_uInt16 ScFuncDesc::GetSuppressedArgCount() const
         return nArgCount;
 
     sal_uInt16 nArgs = nArgCount;
-    if (nArgs >= VAR_ARGS)
+    if (nArgs >= PAIRED_VAR_ARGS)
+        nArgs -= PAIRED_VAR_ARGS - 2;
+    else if (nArgs >= VAR_ARGS)
         nArgs -= VAR_ARGS - 1;
     sal_uInt16 nCount = nArgs;
     for (sal_uInt16 i=0; i < nArgs; ++i)
@@ -251,7 +284,9 @@ sal_uInt16 ScFuncDesc::GetSuppressedArgCount() const
         if (pDefArgFlags[i].bSuppress)
             --nCount;
     }
-    if (nArgCount >= VAR_ARGS)
+    if (nArgCount >= PAIRED_VAR_ARGS)
+        nCount += PAIRED_VAR_ARGS - 2;
+    else if (nArgCount >= VAR_ARGS)
         nCount += VAR_ARGS - 1;
     return nCount;
 }
@@ -295,7 +330,9 @@ void ScFuncDesc::fillVisibleArgumentMapping(::std::vector<sal_uInt16>& _rArgumen
 
     _rArguments.reserve( nArgCount);
     sal_uInt16 nArgs = nArgCount;
-    if (nArgs >= VAR_ARGS)
+    if (nArgs >= PAIRED_VAR_ARGS)
+        nArgs -= PAIRED_VAR_ARGS - 2;
+    else if (nArgs >= VAR_ARGS)
         nArgs -= VAR_ARGS - 1;
     for (sal_uInt16 i=0; i < nArgs; ++i)
     {
@@ -809,7 +846,9 @@ ScFuncRes::ScFuncRes( ResId &aRes, ScFuncDesc* pDesc, bool & rbSuppressed )
     pDesc->sHelpId = ReadByteStringRes();
     pDesc->nArgCount = GetNum();
     sal_uInt16 nArgs = pDesc->nArgCount;
-    if (nArgs >= VAR_ARGS)
+    if (nArgs >= PAIRED_VAR_ARGS)
+        nArgs -= PAIRED_VAR_ARGS - 2;
+    else if (nArgs >= VAR_ARGS)
         nArgs -= VAR_ARGS - 1;
     if (nArgs)
     {
@@ -835,7 +874,12 @@ ScFuncRes::ScFuncRes( ResId &aRes, ScFuncDesc* pDesc, bool & rbSuppressed )
             sal_uInt16 nParam = GetNum();
             if (nParam < nArgs)
             {
-                if (pDesc->nArgCount >= VAR_ARGS && nParam == nArgs-1)
+                if (pDesc->nArgCount >= PAIRED_VAR_ARGS && nParam >= nArgs-2)
+                {
+                    OSL_TRACE( "ScFuncRes: PAIRED_VAR_ARGS parameters can't be suppressed, on OpCode %u: param %d >= arg %d-2",
+                            aRes.GetId(), (int)nParam, (int)nArgs);
+                }
+                else if (pDesc->nArgCount >= VAR_ARGS && nParam == nArgs-1)
                 {
                     OSL_TRACE( "ScFuncRes: VAR_ARGS parameters can't be suppressed, on OpCode %u: param %d == arg %d-1",
                             aRes.GetId(), (int)nParam, (int)nArgs);
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 1c408f89ae75..762b818eadce 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -74,6 +74,13 @@ enum ScIterFuncIf
     ifAVERAGEIF                         // Conditional average
 };
 
+enum ScIterFuncIfs
+{
+    ifSUMIFS,     // Multi-Conditional sum
+    ifAVERAGEIFS, // Multi-Conditional average
+    ifCOUNTIFS    // Multi-Conditional count
+};
+
 struct FormulaTokenRef_less
 {
     bool operator () ( const formula::FormulaConstTokenRef& r1, const formula::FormulaConstTokenRef& r2 ) const
@@ -483,6 +490,10 @@ double IterateParametersIf( ScIterFuncIf );
 void ScCountIf();
 void ScSumIf();
 void ScAverageIf();
+double IterateParametersIfs( ScIterFuncIfs );
+void ScSumIfs();
+void ScAverageIfs();
+void ScCountIfs();
 void ScCountEmptyCells();
 void ScLookup();
 void ScHLookup();
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index a27c3678ad9d..8e592454e784 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -5728,6 +5728,392 @@ void ScInterpreter::ScCountIf()
     }
 }
 
+double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc )
+{
+    sal_uInt8 nParamCount = GetByte();
+    sal_uInt8 nQueryCount = nParamCount / 2;
+
+    bool bCheck;
+    if ( eFunc == ifCOUNTIFS )
+        bCheck = (nParamCount >= 2) && (nParamCount % 2 == 0);
+    else
+        bCheck = (nParamCount >= 3) && (nParamCount % 2 == 1);
+
+    if ( !bCheck )
+    {
+        SetError( errParameterExpected);
+    }
+    else
+    {
+        ScMatrixRef pResMat;
+        double fVal = 0.0;
+        double fSum = 0.0;
+        double fMem = 0.0;
+        double fRes = 0.0;
+        double fCount = 0.0;
+        short nParam = 1;
+        size_t nRefInList = 0;
+
+        while (nParamCount > 1 && !nGlobalError)
+        {
+            // take criteria
+            String rString;
+            fVal = 0.0;
+            bool bIsString = true;
+            switch ( GetStackType() )
+            {
+                case svDoubleRef :
+                case svSingleRef :
+                    {
+                        ScAddress aAdr;
+                        if ( !PopDoubleRefOrSingleRef( aAdr ) )
+                            return 0;
+
+                        ScBaseCell* pCell = GetCell( aAdr );
+                        switch ( GetCellType( pCell ) )
+                        {
+                            case CELLTYPE_VALUE :
+                                fVal = GetCellValue( aAdr, pCell );
+                                bIsString = false;
+                                break;
+                            case CELLTYPE_FORMULA :
+                                if( ((ScFormulaCell*)pCell)->IsValue() )
+                                {
+                                    fVal = GetCellValue( aAdr, pCell );
+                                    bIsString = false;
+                                }
+                                else
+                                    GetCellString(rString, pCell);
+                                break;
+                            case CELLTYPE_STRING :
+                            case CELLTYPE_EDIT :
+                                GetCellString(rString, pCell);
+                                break;
+                            default:
+                                fVal = 0.0;
+                                bIsString = false;
+                        }
+                    }
+                    break;
+                case svString:
+                    rString = GetString();
+                    break;
+                case svMatrix :
+                    {
+                        ScMatValType nType = GetDoubleOrStringFromMatrix( fVal, rString);
+                        bIsString = ScMatrix::IsNonValueType( nType);
+                    }
+                    break;
+                default:
+                    {
+                        fVal = GetDouble();
+                        bIsString = false;
+                    }
+            }
+
+            // take range
+            nParam = 1;
+            nRefInList = 0;
+            SCCOL nCol1;
+            SCROW nRow1;
+            SCTAB nTab1;
+            SCCOL nCol2;
+            SCROW nRow2;
+            SCTAB nTab2;
+            ScMatrixRef pQueryMatrix;
+            switch ( GetStackType() )
+            {
+                case svRefList :
+                    {
+                        ScRange aRange;
+                        PopDoubleRef( aRange, nParam, nRefInList);
+                        aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
+                    }
+                    break;
+                case svDoubleRef :
+                    PopDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
+                    break;
+                case svSingleRef :
+                    PopSingleRef( nCol1, nRow1, nTab1 );
+                    nCol2 = nCol1;
+                    nRow2 = nRow1;
+                    nTab2 = nTab1;
+                    break;
+                case svMatrix:
+                    {
+                        pQueryMatrix = PopMatrix();
+                        if (!pQueryMatrix)
+                        {
+                            SetError( errIllegalParameter);
+                        }
+                        nCol1 = 0;
+                        nRow1 = 0;
+                        nTab1 = 0;
+                        SCSIZE nC, nR;
+                        pQueryMatrix->GetDimensions( nC, nR);
+                        nCol2 = static_cast<SCCOL>(nC - 1);
+                        nRow2 = static_cast<SCROW>(nR - 1);
+                        nTab2 = 0;
+                    }
+                    break;
+                default:
+                    SetError( errIllegalParameter);
+            }
+            if ( nTab1 != nTab2 )
+            {
+                SetError( errIllegalParameter);
+            }
+            // initialize temporary result matrix
+            if (!pResMat)
+            {
+                SCSIZE nResC, nResR;
+                nResC = nCol2 - nCol1 + 1;
+                nResR = nRow2 - nRow1 + 1;
+                pResMat = GetNewMat(nResC, nResR);
+                if (!pResMat)
+                {
+                    SetError( errIllegalParameter);
+                }
+                else
+                {
+                    pResMat->FillDouble( 0.0, 0, 0, nResC-1, nResR-1);
+                }
+            }
+            // recalculate matrix values
+            if (nGlobalError == 0)
+            {
+                ScQueryParam rParam;
+                rParam.nRow1       = nRow1;
+                rParam.nRow2       = nRow2;
+
+                ScQueryEntry& rEntry = rParam.GetEntry(0);
+                ScQueryEntry::Item& rItem = rEntry.GetQueryItem();
+                rEntry.bDoQuery = true;
+                if (!bIsString)
+                {
+                    rItem.meType = ScQueryEntry::ByValue;
+                    rItem.mfVal = fVal;
+                    rEntry.eOp = SC_EQUAL;
+                }
+                else
+                {
+                    rParam.FillInExcelSyntax(rString, 0);
+                    sal_uInt32 nIndex = 0;
+                    bool bNumber = pFormatter->IsNumberFormat(
+                            rItem.maString, nIndex, rItem.mfVal);
+                    rItem.meType = bNumber ? ScQueryEntry::ByValue : ScQueryEntry::ByString;
+                    if (rItem.meType == ScQueryEntry::ByString)
+                        rParam.bRegExp = MayBeRegExp(rItem.maString, pDok);
+                }
+                ScAddress aAdr;
+                aAdr.SetTab( nTab1 );
+                rParam.nCol1  = nCol1;
+                rParam.nCol2  = nCol2;
+                rEntry.nField = nCol1;
+                SCsCOL nColDiff = -nCol1;
+                SCsROW nRowDiff = -nRow1;
+                if (pQueryMatrix)
+                {
+                    // Never case-sensitive.
+                    ScCompareOptions aOptions( pDok, rEntry, rParam.bRegExp);
+                    ScMatrixRef pResultMatrix = QueryMat( pQueryMatrix, aOptions);
+                    if (nGlobalError || !pResultMatrix)
+                    {
+                        SetError( errIllegalParameter);
+                    }
+
+                    for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
+                    {
+                        for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
+                        {
+                            if (pResultMatrix->IsValue( nCol, nRow) &&
+                                    pResultMatrix->GetDouble( nCol, nRow))
+                            {
+                                SCSIZE nC = nCol + nColDiff;
+                                SCSIZE nR = nRow + nRowDiff;
+                                pResMat->PutDouble(pResMat->GetDouble(nC, nR)+1.0, nC, nR);
+                            }
+                        }
+                    }
+                }
+                else
+                {
+                    ScQueryCellIterator aCellIter(pDok, nTab1, rParam, false);
+                    // Increment Entry.nField in iterator when switching to next column.
+                    aCellIter.SetAdvanceQueryParamEntryField( true );
+                    if ( aCellIter.GetFirst() )
+                    {
+                        do
+                        {
+                            SCSIZE nC = aCellIter.GetCol() + nColDiff;
+                            SCSIZE nR = aCellIter.GetRow() + nRowDiff;
+                            pResMat->PutDouble(pResMat->GetDouble(nC, nR)+1.0, nC, nR);
+                        } while ( aCellIter.GetNext() );
+                    }
+                }
+            }
+            else
+            {
+                SetError( errIllegalParameter);
+            }
+            nParamCount -= 2;
+        }
+
+        // main range - only for AVERAGEIFS and SUMIFS
+        if (nParamCount == 1)
+        {
+            nParam = 1;
+            nRefInList = 0;
+            bool bNull = true;
+            SCCOL nMainCol1;
+            SCROW nMainRow1;
+            SCTAB nMainTab1;
+            SCCOL nMainCol2;
+            SCROW nMainRow2;
+            SCTAB nMainTab2;
+            ScMatrixRef pMainMatrix;
+            switch ( GetStackType() )
+            {
+                case svRefList :
+                    {
+                        ScRange aRange;
+                        PopDoubleRef( aRange, nParam, nRefInList);
+                        aRange.GetVars( nMainCol1, nMainRow1, nMainTab1, nMainCol2, nMainRow2, nMainTab2);
+                    }
+                    break;
+                case svDoubleRef :
+                    PopDoubleRef( nMainCol1, nMainRow1, nMainTab1, nMainCol2, nMainRow2, nMainTab2 );
+                    break;
+                case svSingleRef :
+                    PopSingleRef( nMainCol1, nMainRow1, nMainTab1 );
+                    nMainCol2 = nMainCol1;
+                    nMainRow2 = nMainRow1;
+                    nMainTab2 = nMainTab1;
+                    break;
+                case svMatrix:
+                    {
+                        pMainMatrix = PopMatrix();
+                        if (!pMainMatrix)
+                        {
+                            SetError( errIllegalParameter);
+                        }
+                        nMainCol1 = 0;
+                        nMainRow1 = 0;
+                        nMainTab1 = 0;
+                        SCSIZE nC, nR;
+                        pMainMatrix->GetDimensions( nC, nR);
+                        nMainCol2 = static_cast<SCCOL>(nC - 1);
+                        nMainRow2 = static_cast<SCROW>(nR - 1);
+                        nMainTab2 = 0;
+                    }
+                    break;
+                default:
+                    SetError( errIllegalParameter);
+            }
+            if ( nMainTab1 != nMainTab2 )
+            {
+                SetError( errIllegalParameter);
+            }
+            // end-result calculation
+            ScAddress aAdr;
+            aAdr.SetTab( nMainTab1 );
+            if (pMainMatrix)
+            {
+                SCSIZE nC, nR;
+                pResMat->GetDimensions(nC, nR);
+                for (SCSIZE nCol = 0; nCol < nC; ++nCol)
+                {
+                    for (SCSIZE nRow = 0; nRow < nR; ++nRow)
+                    {
+                        if (pResMat->GetDouble( nCol, nRow) == nQueryCount)
+                        {
+                            if (pMainMatrix->IsValue( nCol, nRow))
+                            {
+                                fVal = pMainMatrix->GetDouble( nCol, nRow);
+                                ++fCount;
+                                if ( bNull && fVal != 0.0 )
+                                {
+                                    bNull = false;
+                                    fMem = fVal;
+                                }
+                                else
+                                    fSum += fVal;
+                            }
+                        }
+                    }
+                }
+            }
+            else
+            {
+                SCSIZE nC, nR;
+                pResMat->GetDimensions(nC, nR);
+                for (SCSIZE nCol = 0; nCol < nC; ++nCol)
+                {
+                    for (SCSIZE nRow = 0; nRow < nR; ++nRow)
+                    {
+                        if (pResMat->GetDouble( nCol, nRow) == nQueryCount)
+                        {
+                            aAdr.SetCol( nCol + nMainCol1);
+                            aAdr.SetRow( nRow + nMainRow1);
+                            ScBaseCell* pCell = GetCell( aAdr );
+                            if ( HasCellValueData(pCell) )
+                            {
+                                fVal = GetCellValue( aAdr, pCell );
+                                ++fCount;
+                                if ( bNull && fVal != 0.0 )
+                                {
+                                    bNull = false;
+                                    fMem = fVal;
+                                }
+                                else
+                                    fSum += fVal;
+                            }
+                        }
+                    }
+                }
+            }
+        }
+        else
+        {
+            SCSIZE nC, nR;
+            pResMat->GetDimensions(nC, nR);
+            for (SCSIZE nCol = 0; nCol < nC; ++nCol)
+            {
+                for (SCSIZE nRow = 0; nRow < nR; ++nRow)
+                    if (pResMat->GetDouble( nCol, nRow) == nQueryCount)
+                        ++fCount;
+            }
+        }
+        //
+        switch( eFunc )
+        {
+            case ifSUMIFS:     fRes = ::rtl::math::approxAdd( fSum, fMem ); break;
+            case ifAVERAGEIFS: fRes = div( ::rtl::math::approxAdd( fSum, fMem ), fCount); break;
+            case ifCOUNTIFS:   fRes = fCount; break;
+            default: ; // nothing
+        }
+        return fRes;
+    }
+    return 0;
+}
+
+void ScInterpreter::ScSumIfs()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "makkica", "ScInterpreter::ScSumIfs" );
+    PushDouble( IterateParametersIfs( ifSUMIFS));
+}
+
+void ScInterpreter::ScAverageIfs()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "makkica", "ScInterpreter::ScAverageIfs" );
+    PushDouble( IterateParametersIfs( ifAVERAGEIFS));
+}
+
+void ScInterpreter::ScCountIfs()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "makkica", "ScInterpreter::ScCountIfs" );
+    PushDouble( IterateParametersIfs( ifCOUNTIFS));
+}
 
 void ScInterpreter::ScLookup()
 {
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index e68426c93311..7155445f9a0d 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4055,6 +4055,9 @@ StackVar ScInterpreter::Interpret()
                 case ocCountIf          : ScCountIf();                  break;
                 case ocSumIf            : ScSumIf();                    break;
                 case ocAverageIf        : ScAverageIf();                break;
+                case ocSumIfs           : ScSumIfs();                   break;
+                case ocAverageIfs       : ScAverageIfs();               break;
+                case ocCountIfs         : ScCountIfs();                 break;
                 case ocLookup           : ScLookup();                   break;
                 case ocVLookup          : ScVLookup();                  break;
                 case ocHLookup          : ScHLookup();                  break;
diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx
index 3faa3b81549c..d3ca7f97384e 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -74,6 +74,7 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     { ocAverage,         {{ Reference                                            }, true }},
     { ocAverageA,        {{ Reference                                            }, true }},
     { ocAverageIf,       {{ Reference, Value, Reference                          }, false }},
+    { ocAverageIfs,      {{ Reference, Reference, Value                          }, true }},
     { ocCell,            {{ Value, Reference                                     }, false }},
     { ocColumn,          {{ Reference                                            }, false }},
     { ocColumns,         {{ Reference                                            }, true }},
@@ -82,6 +83,7 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     { ocCount2,          {{ Reference                                            }, true }},
     { ocCountEmptyCells, {{ Reference                                            }, false }},
     { ocCountIf,         {{ Reference, Value                                     }, false }},
+    { ocCountIfs,        {{ Reference, Value                                     }, true }},
     { ocCovar,           {{ ForceArray, ForceArray                               }, false }},
     { ocDBAverage,       {{ Reference, Reference, Reference                      }, false }},
     { ocDBCount,         {{ Reference, Reference, Reference                      }, false }},
@@ -167,6 +169,7 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     { ocSubTotal,        {{ Value, Reference                                     }, true }},
     { ocSum,             {{ Reference                                            }, true }},
     { ocSumIf,           {{ Reference, Value, Reference                          }, false }},
+    { ocSumIfs,          {{ Reference, Reference, Value                          }, true }},
     { ocSumProduct,      {{ ForceArray                                           }, true }},
     { ocSumSQ,           {{ Reference                                            }, true }},
     { ocSumX2MY2,        {{ ForceArray, ForceArray                               }, false }},
@@ -404,7 +407,12 @@ void ScParameterClassification::MergeArgumentsFromFunctionResource()
 
         RunData* pRun = &pData[ pDesc->nFIndex ];
         sal_uInt16 nArgs = pDesc->GetSuppressedArgCount();
-        if ( nArgs >= VAR_ARGS )
+        if ( nArgs >= PAIRED_VAR_ARGS )
+        {
+            nArgs -= PAIRED_VAR_ARGS - 2;
+            pRun->aData.bRepeatLast = true;
+        }
+        else if ( nArgs >= VAR_ARGS )
         {
             nArgs -= VAR_ARGS - 1;
             pRun->aData.bRepeatLast = true;
diff --git a/sc/source/ui/formdlg/dwfunctr.cxx b/sc/source/ui/formdlg/dwfunctr.cxx
index 8d48abb55f36..cc978c0f7866 100644
--- a/sc/source/ui/formdlg/dwfunctr.cxx
+++ b/sc/source/ui/formdlg/dwfunctr.cxx
@@ -867,10 +867,16 @@ void ScFunctionDockWin::DoEnter(sal_Bool /* bOk */) //@@ ???
                 aFirstArgStr = comphelper::string::strip(aFirstArgStr, ' ');
                 aFirstArgStr.SearchAndReplaceAll(' ', '_');
                 aArgStr = aFirstArgStr;
-                if ( nArgs != VAR_ARGS )
+                if ( nArgs != VAR_ARGS && nArgs != PAIRED_VAR_ARGS )
                 {   // no VarArgs or Fix plus VarArgs, but not VarArgs only
                     rtl::OUString aArgSep("; ");
-                    sal_uInt16 nFix = ( nArgs < VAR_ARGS ? nArgs : nArgs - VAR_ARGS + 1 );
+                    sal_uInt16 nFix;
+                    if (nArgs >= PAIRED_VAR_ARGS)
+                        nFix = nArgs - PAIRED_VAR_ARGS + 2;
+                    else if (nArgs >= VAR_ARGS)
+                        nFix = nArgs - VAR_ARGS + 1;
+                    else
+                        nFix = nArgs;
                     for ( sal_uInt16 nArg = 1;
                             nArg < nFix && !pDesc->pDefArgFlags[nArg].bOptional; nArg++ )
                     {
diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src
index e7f986d28a8b..b24b0b130823 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -2970,6 +2970,121 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
 			Text [ en-US ] = "The range from which the values are to be averaged." ;
 		};
 	};
+
+	Resource SC_OPCODE_SUM_IFS
+	{
+		String 1 // Description
+		{
+			Text [ en-US ] = "Totals the values of cells in a range that meet multiple criteria in multiple ranges." ;
+		};
+		ExtraData =
+		{
+            0;
+			ID_FUNCTION_GRP_MATH;
+			U2S( HID_FUNC_SUMIFS );
+			PAIRED_VAR_ARGS+1;	0;	0;	0;
+            0;
+		};
+		String 2 // Name of Parameter 1
+		{
+			Text [ en-US ] = "sum_range" ;
+		};
+		String 3 // Description of Parameter 1
+		{
+			Text [ en-US ] = "The range from which the values are to be totalled." ;
+		};
+		String 4 // Name of Parameter 2
+		{
+			Text [ en-US ] = "range" ;
+		};
+		String 5 // Description of Parameter 2
+		{
+			Text [ en-US ] = "Range 1, range 2,... are the ranges to be evaluated by the criteria given." ;
+		};
+		String 6 // Name of Parameter 3
+		{
+			Text [ en-US ] = "criteria" ;
+		};
+		String 7 // Description of Parameter 3
+		{
+			Text [ en-US ] = "Criteria 1, criteria 2,... are the cell ranges in which the search criteria are given." ;
+		};
+	};
+
+	// -=*# Resource for function AVERAGEIFS #*=-
+	Resource SC_OPCODE_AVERAGE_IFS
+	{
+		String 1 // Description
+		{
+			Text [ en-US ] = "Averages the value of the cells that meet multiple criteria in multiple ranges." ;
+		};
+		ExtraData =
+		{
+            0;
+			ID_FUNCTION_GRP_MATH;
+			U2S( HID_FUNC_AVERAGEIFS );
+			PAIRED_VAR_ARGS+1;	0;	0;	0;
+            0;
+		};
+		String 2 // Name of Parameter 1
+		{
+			Text [ en-US ] = "average_range" ;
+		};
+		String 3 // Description of Parameter 1
+		{
+			Text [ en-US ] = "The range from which the values are to be averaged." ;
+		};
+		String 4 // Name of Parameter 2
+		{
+			Text [ en-US ] = "range" ;
+		};
+		String 5 // Description of Parameter 2
+		{
+			Text [ en-US ] = "Range 1, range 2,... are the ranges to be evaluated by the criteria given." ;
+		};
+		String 6 // Name of Parameter 3
+		{
+			Text [ en-US ] = "criteria" ;
+		};
+		String 7 // Description of Parameter 3
+		{
+			Text [ en-US ] = "Criteria 1, criteria 2,... are the cell ranges in which the search criteria are given." ;
+		};
+	};
+
+	// -=*# Resource for function COUNTIFS #*=-
+	Resource SC_OPCODE_COUNT_IFS
+	{
+		String 1 // Description
+		{
+			Text [ en-US ] = "Counts the cells that meet multiple criteria in multiple ranges." ;
+		};
+		ExtraData =
+		{
+            0;
+			ID_FUNCTION_GRP_MATH;
+			U2S( HID_FUNC_COUNTIFS );
+			PAIRED_VAR_ARGS;	0;	0;
+            0;
+		};
+		String 2 // Name of Parameter 1
+		{
+			Text [ en-US ] = "range" ;
+		};
+		String 3 // Description of Parameter 1
+		{
+			Text [ en-US ] = "Range 1, range 2,... are the ranges to be evaluated by the criteria given." ;
+		};
+		String 4 // Name of Parameter 2
+		{
+			Text [ en-US ] = "criteria" ;
+		};
+		String 5 // Description of Parameter 2
+		{
+			Text [ en-US ] = "Criteria 1, criteria 2,... are the cell ranges in which the search criteria are given." ;
+		};
+	};
+
      // -=*# Resource for function ZÄHLENWENN #*=-
     Resource SC_OPCODE_COUNT_IF
     {
diff --git a/sc/source/ui/unoobj/appluno.cxx b/sc/source/ui/unoobj/appluno.cxx
index adbb57253333..95a4c356a149 100644
--- a/sc/source/ui/unoobj/appluno.cxx
+++ b/sc/source/ui/unoobj/appluno.cxx
@@ -748,10 +748,14 @@ static void lcl_FillSequence( uno::Sequence<beans::PropertyValue>& rSequence, co
     if (rDesc.ppDefArgNames && rDesc.ppDefArgDescs && rDesc.pDefArgFlags )
     {
         sal_uInt16 nCount = rDesc.nArgCount;
-        if (nCount >= VAR_ARGS)
+		if (nCount >= PAIRED_VAR_ARGS)
+		    nCount -= PAIRED_VAR_ARGS - 2;
+        else if (nCount >= VAR_ARGS)
             nCount -= VAR_ARGS - 1;
         sal_uInt16 nSeqCount = rDesc.GetSuppressedArgCount();
-        if (nSeqCount >= VAR_ARGS)
+        if (nSeqCount >= PAIRED_VAR_ARGS)
+            nSeqCount -= PAIRED_VAR_ARGS - 2;
+        else if (nSeqCount >= VAR_ARGS)
             nSeqCount -= VAR_ARGS - 1;
 
         if (nSeqCount)
diff --git a/sc/util/hidother.src b/sc/util/hidother.src
index 719a677002d8..d33922911cc3 100644
--- a/sc/util/hidother.src
+++ b/sc/util/hidother.src
@@ -162,6 +162,9 @@ hidspecial HID_FUNC_PRODUKT      { HelpID = HID_FUNC_PRODUKT; };
 hidspecial HID_FUNC_SUMMEWENN        { HelpID = HID_FUNC_SUMMEWENN; };
 hidspecial HID_FUNC_ZAEHLENWENN      { HelpID = HID_FUNC_ZAEHLENWENN; };
 hidspecial HID_FUNC_AVERAGEIF		 { HelpID = HID_FUNC_AVERAGEIF; };
+hidspecial HID_FUNC_SUMIFS		 { HelpID = HID_FUNC_SUMIFS; };
+hidspecial HID_FUNC_AVERAGEIFS		 { HelpID = HID_FUNC_AVERAGEIFS; };
+hidspecial HID_FUNC_COUNTIFS		 { HelpID = HID_FUNC_COUNTIFS; };
 hidspecial HID_FUNC_WURZEL       { HelpID = HID_FUNC_WURZEL; };
 hidspecial HID_FUNC_ZUFALLSZAHL      { HelpID = HID_FUNC_ZUFALLSZAHL; };
 hidspecial HID_FUNC_ISTGERADE        { HelpID = HID_FUNC_ISTGERADE; };