This is an automated email from the ASF dual-hosted git repository. centic9 pushed a commit to branch adjust_countifs in repository https://gitbox.apache.org/repos/asf/poi.git
commit ada626ad432b095eff46211fade5216a0a20349e Author: Dominik Stadler <[email protected]> AuthorDate: Mon May 4 19:10:51 2026 +0200 Bug 70005: Fix countifs with multi-element arrays Actual fix provided by Claude Code --- .../ss/tests/formula/functions/TestCountifs.java | 39 ++++++++++++++++ .../apache/poi/ss/formula/functions/Baseifs.java | 52 ++++++++++++++++++--- test-data/spreadsheet/70005-countifs.xlsx | Bin 0 -> 5971 bytes 3 files changed, 84 insertions(+), 7 deletions(-) diff --git a/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/functions/TestCountifs.java b/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/functions/TestCountifs.java index 902de11a80..41efde0c13 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/functions/TestCountifs.java +++ b/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/functions/TestCountifs.java @@ -35,6 +35,7 @@ import org.apache.poi.xssf.XSSFTestDataSamples; import org.junit.jupiter.api.AfterEach; import org.junit.jupiter.api.BeforeEach; import org.junit.jupiter.api.Test; +import org.junit.jupiter.api.Disabled; /** * Test the COUNTIFS() function @@ -118,4 +119,42 @@ class TestCountifs { CellValue evaluate = evaluator.evaluate(cell); assertEquals(2.0d, evaluate.getNumberValue(), 0.00000000000001); } + + /** + * Bug 70005 - SUM(COUNTIFS) with multiple values in criteria gives wrong result, + * and uses wrong area check causing ERROR when formula cell is not in the data range rows. + * Expected: A3=3.0, A4=4.0, D3=3.0, D4=4.0 (verified in Excel and LibreOffice Calc). + */ + @Test + void testBug70005() { + workbook = XSSFTestDataSamples.openSampleWorkbook("70005-countifs.xlsx"); + FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); + Sheet sheet = workbook.getSheetAt(0); + + // A3 and A4: formula cell is within the data area rows - wrong numeric result in buggy code + Cell a3 = SheetUtil.getCell(sheet, 2, 0); + assertNotNull(a3, "Test workbook missing cell A3"); + CellValue a3Value = evaluator.evaluate(a3); + assertEquals(CellType.NUMERIC, a3Value.getCellType(), "A3 should be numeric, not an error"); + assertEquals(3.0, a3Value.getNumberValue(), 0.00000000000001, "A3: SUM(COUNTIFS) with multiple criteria should equal 3"); + + Cell a4 = SheetUtil.getCell(sheet, 3, 0); + assertNotNull(a4, "Test workbook missing cell A4"); + CellValue a4Value = evaluator.evaluate(a4); + assertEquals(CellType.NUMERIC, a4Value.getCellType(), "A4 should be numeric, not an error"); + assertEquals(4.0, a4Value.getNumberValue(), 0.00000000000001, "A4: SUM(COUNTIFS) with multiple criteria should equal 4"); + + // D3 and D4: formula cell is outside the data area rows - buggy code returns ERROR here + Cell d3 = SheetUtil.getCell(sheet, 2, 3); + assertNotNull(d3, "Test workbook missing cell D3"); + CellValue d3Value = evaluator.evaluate(d3); + assertEquals(CellType.NUMERIC, d3Value.getCellType(), "D3 should be numeric, not an error"); + assertEquals(3.0, d3Value.getNumberValue(), 0.00000000000001, "D3: SUM(COUNTIFS) with formula cell outside data rows should equal 3"); + + Cell d4 = SheetUtil.getCell(sheet, 3, 3); + assertNotNull(d4, "Test workbook missing cell D4"); + CellValue d4Value = evaluator.evaluate(d4); + assertEquals(CellType.NUMERIC, d4Value.getCellType(), "D4 should be numeric, not an error"); + assertEquals(4.0, d4Value.getNumberValue(), 0.00000000000001, "D4: SUM(COUNTIFS) with formula cell outside data rows should equal 4"); + } } diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java index 64ebc533be..d6e8d655d9 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Baseifs.java @@ -23,6 +23,7 @@ import org.apache.poi.ss.formula.OperationEvaluationContext; import org.apache.poi.ss.formula.eval.AreaEval; import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.NumberEval; import org.apache.poi.ss.formula.eval.RefEval; import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate; @@ -64,18 +65,55 @@ import org.apache.poi.ss.formula.functions.Countif.ErrorMatcher; sumRange = convertRangeArg(args[0]); } - // collect pairs of ranges and criteria - AreaEval[] ae = new AreaEval[(args.length - firstCriteria)/2]; - I_MatchPredicate[] mp = new I_MatchPredicate[ae.length]; - for(int i = firstCriteria, k=0; i < (args.length - 1); i += 2, k++){ + int numPairs = (args.length - firstCriteria) / 2; + AreaEval[] ae = new AreaEval[numPairs]; + ValueEval[] criteriaArgs = new ValueEval[numPairs]; + for (int i = firstCriteria, k = 0; i < (args.length - 1); i += 2, k++) { ae[k] = convertRangeArg(args[i]); - - mp[k] = Countif.createCriteriaPredicate(args[i+1], ec.getRowIndex(), ec.getColumnIndex()); + criteriaArgs[k] = args[i + 1]; } validateCriteriaRanges(sumRange, ae); - validateCriteria(mp); + // If any criteria argument is a multi-element array (e.g. {1,2,3} or a + // multi-cell range), expand it: evaluate once per element and sum the + // results. This supports SUM(COUNTIFS(range, {v1,v2,...})) patterns where + // the implicit-intersection approach used by getSingleValue would either + // yield the wrong single value or produce an ERROR when the formula cell + // lies outside the array's row/column bounds (Bug 70005). + for (int k = 0; k < numPairs; k++) { + if (criteriaArgs[k] instanceof AreaEval) { + AreaEval arrayCrit = (AreaEval) criteriaArgs[k]; + if (arrayCrit.getHeight() * arrayCrit.getWidth() > 1) { + double total = 0.0; + for (int r = 0; r < arrayCrit.getHeight(); r++) { + for (int c = 0; c < arrayCrit.getWidth(); c++) { + ValueEval element = arrayCrit.getRelativeValue(r, c); + I_MatchPredicate[] mp = new I_MatchPredicate[numPairs]; + for (int j = 0; j < numPairs; j++) { + mp[j] = Countif.createCriteriaPredicate( + j == k ? element : criteriaArgs[j], + ec.getRowIndex(), ec.getColumnIndex()); + } + validateCriteria(mp); + ValueEval partial = aggregateMatchingCells(createAggregator(), sumRange, ae, mp); + if (partial instanceof ErrorEval) return partial; + if (partial instanceof NumberEval) { + total += ((NumberEval) partial).getNumberValue(); + } + } + } + return new NumberEval(total); + } + } + } + + // All criteria are scalar — normal single-pass evaluation + I_MatchPredicate[] mp = new I_MatchPredicate[numPairs]; + for (int k = 0; k < numPairs; k++) { + mp[k] = Countif.createCriteriaPredicate(criteriaArgs[k], ec.getRowIndex(), ec.getColumnIndex()); + } + validateCriteria(mp); return aggregateMatchingCells(createAggregator(), sumRange, ae, mp); } catch (EvaluationException e) { return e.getErrorEval(); diff --git a/test-data/spreadsheet/70005-countifs.xlsx b/test-data/spreadsheet/70005-countifs.xlsx new file mode 100644 index 0000000000..deed72fbdc Binary files /dev/null and b/test-data/spreadsheet/70005-countifs.xlsx differ --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
