You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ce...@apache.org on 2026/05/13 05:43:32 UTC
(poi) branch adjust_countifs created (now b73413a962)
This is an automated email from the ASF dual-hosted git repository.
centic9 pushed a change to branch adjust_countifs
in repository https://gitbox.apache.org/repos/asf/poi.git
at b73413a962 Increase coverage for function CountIf
This branch includes the following new commits:
new ada626ad43 Bug 70005: Fix countifs with multi-element arrays
new b73413a962 Increase coverage for function CountIf
The 2 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org
(poi) 01/02: Bug 70005: Fix countifs with multi-element arrays
Posted by ce...@apache.org.
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 <do...@dynatrace.com>
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: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org
(poi) 02/02: Increase coverage for function CountIf
Posted by ce...@apache.org.
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 b73413a962a20dfcb9520597d7f729978af0eeb1
Author: Dominik Stadler <do...@dynatrace.com>
AuthorDate: Wed May 6 21:17:08 2026 +0200
Increase coverage for function CountIf
Co-authored-by: Copilot
---
.../ss/tests/formula/functions/TestCountifs.java | 237 ++++++++++++++++++++-
1 file changed, 236 insertions(+), 1 deletion(-)
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 41efde0c13..b420ad7464 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,7 +35,6 @@ 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
@@ -157,4 +156,240 @@ class TestCountifs {
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");
}
+
+ /** Minimum valid case: a single criteria range/criteria pair. */
+ @Test
+ void testSingleCriteriaPair() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row = sheet.createRow(0);
+ Cell formulaCell = row.createCell(0, CellType.FORMULA);
+ row.createCell(1).setCellValue(1.0);
+ row.createCell(2).setCellValue(2.0);
+ row.createCell(3).setCellValue(1.0);
+
+ formulaCell.setCellFormula("COUNTIFS(B1:D1,1)");
+ CellValue result = workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell);
+ assertEquals(2.0d, result.getNumberValue(), 0.000000000000001);
+ }
+
+ /** When no cell matches the criteria, COUNTIFS must return 0. */
+ @Test
+ void testReturnsZeroWhenNothingMatches() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row = sheet.createRow(0);
+ Cell formulaCell = row.createCell(0, CellType.FORMULA);
+ row.createCell(1).setCellValue(5.0);
+ row.createCell(2).setCellValue(6.0);
+
+ formulaCell.setCellFormula("COUNTIFS(B1:C1,99)");
+ CellValue result = workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell);
+ assertEquals(0.0d, result.getNumberValue(), 0.000000000000001);
+ }
+
+ /** When every cell in the range matches, COUNTIFS returns the range size. */
+ @Test
+ void testAllCellsMatchCriteria() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row = sheet.createRow(0);
+ Cell formulaCell = row.createCell(0, CellType.FORMULA);
+ row.createCell(1).setCellValue(3.0);
+ row.createCell(2).setCellValue(3.0);
+ row.createCell(3).setCellValue(3.0);
+
+ formulaCell.setCellFormula("COUNTIFS(B1:D1,3)");
+ CellValue result = workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell);
+ assertEquals(3.0d, result.getNumberValue(), 0.000000000000001);
+ }
+
+ /** String criteria must match cell values exactly (but case-insensitively). */
+ @Test
+ void testStringCriteriaMatching() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row = sheet.createRow(0);
+ Cell formulaCell = row.createCell(0, CellType.FORMULA);
+ row.createCell(1).setCellValue("apple");
+ row.createCell(2).setCellValue("banana");
+ row.createCell(3).setCellValue("apple");
+
+ formulaCell.setCellFormula("COUNTIFS(B1:D1,\"apple\")");
+ CellValue result = workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell);
+ assertEquals(2.0d, result.getNumberValue(), 0.000000000000001);
+ }
+
+ /** String matching in COUNTIFS is case-insensitive, matching Excel behaviour. */
+ @Test
+ void testCaseInsensitiveStringCriteria() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row = sheet.createRow(0);
+ Cell formulaCell = row.createCell(0, CellType.FORMULA);
+ row.createCell(1).setCellValue("Apple");
+ row.createCell(2).setCellValue("APPLE");
+ row.createCell(3).setCellValue("banana");
+
+ formulaCell.setCellFormula("COUNTIFS(B1:D1,\"apple\")");
+ CellValue result = workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell);
+ assertEquals(2.0d, result.getNumberValue(), 0.000000000000001);
+ }
+
+ /** Comparison-operator string criteria: >, >=, <, <=, <> */
+ @Test
+ void testComparisonOperatorCriteria() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row = sheet.createRow(0);
+ Cell formulaCell = row.createCell(0, CellType.FORMULA);
+ row.createCell(1).setCellValue(1.0);
+ row.createCell(2).setCellValue(3.0);
+ row.createCell(3).setCellValue(5.0);
+ row.createCell(4).setCellValue(7.0);
+
+ formulaCell.setCellFormula("COUNTIFS(B1:E1,\">3\")");
+ assertEquals(2.0d, workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell).getNumberValue(), 0.000000000000001);
+
+ formulaCell.setCellFormula("COUNTIFS(B1:E1,\">=3\")");
+ assertEquals(3.0d, workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell).getNumberValue(), 0.000000000000001);
+
+ formulaCell.setCellFormula("COUNTIFS(B1:E1,\"<5\")");
+ assertEquals(2.0d, workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell).getNumberValue(), 0.000000000000001);
+
+ formulaCell.setCellFormula("COUNTIFS(B1:E1,\"<>3\")");
+ assertEquals(3.0d, workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell).getNumberValue(), 0.000000000000001);
+ }
+
+ /** Wildcard characters * (zero-or-more) and ? (exactly one) in string criteria. */
+ @Test
+ void testWildcardCriteria() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row = sheet.createRow(0);
+ Cell formulaCell = row.createCell(0, CellType.FORMULA);
+ row.createCell(1).setCellValue("apple");
+ row.createCell(2).setCellValue("application");
+ row.createCell(3).setCellValue("banana");
+ row.createCell(4).setCellValue("apt");
+
+ // "app*" matches "apple" and "application"
+ formulaCell.setCellFormula("COUNTIFS(B1:E1,\"app*\")");
+ assertEquals(2.0d, workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell).getNumberValue(), 0.000000000000001);
+
+ // "ap?" matches exactly 3-character strings starting with "ap": only "apt"
+ formulaCell.setCellFormula("COUNTIFS(B1:E1,\"ap?\")");
+ assertEquals(1.0d, workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell).getNumberValue(), 0.000000000000001);
+ }
+
+ /**
+ * Multi-row ranges: AND logic must hold for every row position across all criteria ranges.
+ * Only rows where every criterion is satisfied contribute to the count.
+ */
+ @Test
+ void testMultiRowRangeWithAndLogic() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row0 = sheet.createRow(0);
+ Row row1 = sheet.createRow(1);
+ Row row2 = sheet.createRow(2);
+
+ Cell formulaCell = row0.createCell(0, CellType.FORMULA);
+ // row 0: B=1, C=10 both criteria match → counted
+ // row 1: B=1, C=20 second doesn't match → not counted
+ // row 2: B=2, C=10 first doesn't match → not counted
+ row0.createCell(1).setCellValue(1.0); row0.createCell(2).setCellValue(10.0);
+ row1.createCell(1).setCellValue(1.0); row1.createCell(2).setCellValue(20.0);
+ row2.createCell(1).setCellValue(2.0); row2.createCell(2).setCellValue(10.0);
+
+ formulaCell.setCellFormula("COUNTIFS(B1:B3,1,C1:C3,10)");
+ CellValue result = workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell);
+ assertEquals(1.0d, result.getNumberValue(), 0.000000000000001);
+ }
+
+ /** Three criteria pairs — AND logic across all three must hold. */
+ @Test
+ void testThreeCriteriaPairs() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row0 = sheet.createRow(0);
+ Row row1 = sheet.createRow(1);
+ Row row2 = sheet.createRow(2);
+
+ Cell formulaCell = row0.createCell(0, CellType.FORMULA);
+ // row 0: B=1, C=2, D=3 all three match → counted
+ // row 1: B=1, C=2, D=9 third doesn't match → not counted
+ // row 2: B=1, C=9, D=3 second doesn't match → not counted
+ row0.createCell(1).setCellValue(1.0); row0.createCell(2).setCellValue(2.0); row0.createCell(3).setCellValue(3.0);
+ row1.createCell(1).setCellValue(1.0); row1.createCell(2).setCellValue(2.0); row1.createCell(3).setCellValue(9.0);
+ row2.createCell(1).setCellValue(1.0); row2.createCell(2).setCellValue(9.0); row2.createCell(3).setCellValue(3.0);
+
+ formulaCell.setCellFormula("COUNTIFS(B1:B3,1,C1:C3,2,D1:D3,3)");
+ CellValue result = workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell);
+ assertEquals(1.0d, result.getNumberValue(), 0.000000000000001);
+ }
+
+ /**
+ * Criteria ranges with incompatible shapes (different row/column counts) must return
+ * VALUE_INVALID (error code 15).
+ */
+ @Test
+ void testMismatchedCriteriaRangeSizesReturnsError() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row0 = sheet.createRow(0);
+ Row row1 = sheet.createRow(1);
+ Cell formulaCell = row0.createCell(0, CellType.FORMULA);
+ row0.createCell(1).setCellValue(1.0); row0.createCell(2).setCellValue(1.0);
+ row1.createCell(1).setCellValue(2.0);
+
+ // B1:C1 is 1×2, B1:B2 is 2×1 — incompatible shapes
+ formulaCell.setCellFormula("COUNTIFS(B1:C1,1,B1:B2,1)");
+ CellValue result = workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell);
+ assertEquals(15, result.getErrorValue());
+ }
+
+ /**
+ * Empty-string criteria ("") must match blank cells, exercising the BlankEval branch in
+ * StringOperandMatcher.
+ */
+ @Test
+ void testBlankCellsMatchedByEmptyCriteria() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row = sheet.createRow(0);
+ Cell formulaCell = row.createCell(0, CellType.FORMULA);
+ row.createCell(1).setCellValue(1.0); // B1 non-blank
+ row.createCell(2, CellType.BLANK); // C1 blank
+ row.createCell(3).setCellValue(2.0); // D1 non-blank
+ row.createCell(4, CellType.BLANK); // E1 blank
+
+ formulaCell.setCellFormula("COUNTIFS(B1:E1,\"\")");
+ CellValue result = workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell);
+ assertEquals(2.0d, result.getNumberValue(), 0.000000000000001);
+ }
+
+ /**
+ * A single-cell range reference (RefEval) must be accepted as a criteria range by
+ * convertRangeArg, which converts it to a 1×1 AreaEval.
+ */
+ @Test
+ void testSingleCellRange() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row = sheet.createRow(0);
+ Cell formulaCell = row.createCell(0, CellType.FORMULA);
+ row.createCell(1).setCellValue(5.0); // B1 = 5
+
+ formulaCell.setCellFormula("COUNTIFS(B1,5)");
+ CellValue result = workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell);
+ assertEquals(1.0d, result.getNumberValue(), 0.000000000000001);
+ }
+
+ /**
+ * Criteria supplied as a cell reference (RefEval) is dereferenced before matching,
+ * exercising the evaluateCriteriaArg path in Countif.createCriteriaPredicate.
+ */
+ @Test
+ void testCriteriaFromCellReference() {
+ Sheet sheet = workbook.createSheet("test");
+ Row row = sheet.createRow(0);
+ Cell formulaCell = row.createCell(0, CellType.FORMULA); // A1
+ row.createCell(1).setCellValue(1.0); // B1
+ row.createCell(2).setCellValue(2.0); // C1
+ row.createCell(3).setCellValue(1.0); // D1
+ row.createCell(4).setCellValue(1.0); // E1 — used as criteria value
+
+ formulaCell.setCellFormula("COUNTIFS(B1:D1,E1)");
+ CellValue result = workbook.getCreationHelper().createFormulaEvaluator().evaluate(formulaCell);
+ assertEquals(2.0d, result.getNumberValue(), 0.000000000000001);
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org