--- src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (revision 1231476) +++ src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (working copy) @@ -15,6 +15,7 @@ import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.ss.formula.functions.FreeRefFunction; +import org.apache.poi.ss.formula.functions.Sumifs; import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.formula.OperationEvaluationContext; import org.apache.poi.ss.formula.eval.NotImplementedException; @@ -150,7 +151,7 @@ r(m, "RTD", null); r(m, "SERIESSUM", null); r(m, "SQRTPI", null); - r(m, "SUMIFS", null); + r(m, "SUMIFS", Sumifs.instance); r(m, "TBILLEQ", null); r(m, "TBILLPRICE", null); r(m, "TBILLYIELD", null); --- src/java/org/apache/poi/ss/formula/functions/Sumifs.java (revision 0) +++ src/java/org/apache/poi/ss/formula/functions/Sumifs.java (working copy) @@ -0,0 +1,120 @@ +/* + * ==================================================================== + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * ==================================================================== + */ + +package org.apache.poi.ss.formula.functions; + +import org.apache.poi.ss.formula.OperationEvaluationContext; +import org.apache.poi.ss.formula.eval.*; +import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate; + +/** + * Implementation for the Excel function SUMIFS

+ * + * Syntax :
+ * SUMIFS ( sum_range, criteria_range1, criteria1, + * [criteria_range2, criteria2], …)
+ *

+ *

+ */ +public final class Sumifs implements FreeRefFunction { + public static final FreeRefFunction instance = new Sumifs(); + + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + if(args.length < 3 || args.length % 2 == 0) { + return ErrorEval.VALUE_INVALID; + } + + try { + AreaEval sumRange = convertRangeArg(args[0]); + + AreaEval[] ae = new AreaEval[(args.length -1)/2]; + I_MatchPredicate[] mp = new I_MatchPredicate[ae.length]; + for(int i = 1, k=0; i < args.length; i += 2, k++){ + ae[k] = convertRangeArg(args[i]); + mp[k] = Countif.createCriteriaPredicate(args[i+1], ec.getRowIndex(), ec.getColumnIndex()); + } + + double result = sumMatchingCells(ae, mp, sumRange); + return new NumberEval(result); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } + + + private static double sumMatchingCells(AreaEval[] ranges, I_MatchPredicate[] predicates, AreaEval aeSum) { + int height = aeSum.getHeight(); + int width = aeSum.getWidth(); + + double result = 0.0; + for (int r = 0; r < height; r++) { + for (int c = 0; c < width; c++) { + + boolean matches = true; + for(int i = 0; i < ranges.length; i++){ + AreaEval aeRange = ranges[i]; + I_MatchPredicate mp = predicates[i]; + + if (!mp.matches(aeRange.getRelativeValue(r, c))) { + matches = false; + break; + } + + } + + if(matches) { // count only if all the predicate match + result += accumulate(aeSum, r, c); + } + } + } + return result; + } + + private static double accumulate(AreaEval aeSum, int relRowIndex, + int relColIndex) { + + ValueEval addend = aeSum.getRelativeValue(relRowIndex, relColIndex); + if (addend instanceof NumberEval) { + return ((NumberEval)addend).getNumberValue(); + } + // everything else (including string and boolean values) counts as zero + return 0.0; + } + + private static AreaEval convertRangeArg(ValueEval eval) throws EvaluationException { + if (eval instanceof AreaEval) { + return (AreaEval) eval; + } + if (eval instanceof RefEval) { + return ((RefEval)eval).offset(0, 0, 0, 0); + } + throw new EvaluationException(ErrorEval.VALUE_INVALID); + } + +} --- src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java (revision 0) +++ src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java (working copy) @@ -0,0 +1,146 @@ +/* + * ==================================================================== + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * ==================================================================== + */ + +package org.apache.poi.ss.formula.functions; + +import junit.framework.AssertionFailedError; +import junit.framework.TestCase; +import org.apache.poi.ss.formula.OperationEvaluationContext; +import org.apache.poi.ss.formula.eval.*; + +/** + * Test cases for SUMIFS() + * + * @author Yegor Kozlov + */ +public final class TestSumifs extends TestCase { + + private static final OperationEvaluationContext EC = new OperationEvaluationContext(null, null, 0, 1, 0, null); + + private static ValueEval invokeSumifs(ValueEval[] args, OperationEvaluationContext ec) { + return new Sumifs().evaluate(args, EC); + } + private static void confirmDouble(double expected, ValueEval actualEval) { + if(!(actualEval instanceof NumericValueEval)) { + throw new AssertionFailedError("Expected numeric result"); + } + NumericValueEval nve = (NumericValueEval)actualEval; + assertEquals(expected, nve.getNumberValue(), 0); + } + + private static void confirm(double expectedResult, ValueEval[] args) { + confirmDouble(expectedResult, invokeSumifs(args, EC)); + } + + /** + * Example 1 from + * http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx + */ + public void testExample1() { + // mimic test sample from http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx + ValueEval[] a2a9 = new ValueEval[] { + new NumberEval(5), + new NumberEval(4), + new NumberEval(15), + new NumberEval(3), + new NumberEval(22), + new NumberEval(12), + new NumberEval(10), + new NumberEval(33) + }; + + ValueEval[] b2b9 = new ValueEval[] { + new StringEval("Apples"), + new StringEval("Apples"), + new StringEval("Artichokes"), + new StringEval("Artichokes"), + new StringEval("Bananas"), + new StringEval("Bananas"), + new StringEval("Carrots"), + new StringEval("Carrots"), + }; + + ValueEval[] c2c9 = new ValueEval[] { + new NumberEval(1), + new NumberEval(2), + new NumberEval(1), + new NumberEval(2), + new NumberEval(1), + new NumberEval(2), + new NumberEval(1), + new NumberEval(2) + }; + + ValueEval[] args; + // "=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, 1)" + args = new ValueEval[]{ + EvalFactory.createAreaEval("A2:A9", a2a9), + EvalFactory.createAreaEval("B2:B9", b2b9), + new StringEval("A*"), + EvalFactory.createAreaEval("C2:C9", c2c9), + new NumberEval(1), + }; + confirm(20.0, args); + + // "=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, 1)" + args = new ValueEval[]{ + EvalFactory.createAreaEval("A2:A9", a2a9), + EvalFactory.createAreaEval("B2:B9", b2b9), + new StringEval("<>Bananas"), + EvalFactory.createAreaEval("C2:C9", c2c9), + new NumberEval(1), + }; + confirm(30.0, args); + + } + + /** + * Example 2 from + * http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx + */ + public void testExample2() { + } + + /** + * Example 3 from + * http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx + */ + public void testExample3() { + } + + /** + * Example 4 from + * http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx + */ + public void testExample4() { + } + + /** + * Example 5 from + * http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx + */ + public void testExample5() { + } + + /** + * Test passing invalid or wrong arguments + */ + public void testInvalidArguments() { + } +}