pjfanning opened a new pull request, #1323: URL: https://github.com/apache/poi/pull/1323
https://bz.apache.org/bugzilla/show_bug.cgi?id=65059 — `SUMPRODUCT(SUMIFS(B1:B3, C1:C3, D1:D3))`, reported against Office 365 with expected 18. ## What Excel does (verified, not taken from the report) When the criteria argument of SUMIF/COUNTIF/AVERAGEIF/SUMIFS/COUNTIFS/AVERAGEIFS/MAXIFS/MINIFS stands for several criteria, Excel evaluates the function once per criterion and returns an **array**, one result per criterion; the enclosing function consumes it (`SUMPRODUCT(SUMIF(range,criteria_range,sum_range))`, `SUM(COUNTIF(range,{"a","b"}))`, `MAX(COUNTIFS(...))`, `INDEX(SUMIFS(...),2)`). Two rules govern when that happens, and both are visible in files Excel itself produced: - an **array constant** (or a computed array) is always expanded — bug 70005's `SUM(COUNTIFS(range,{v1,v2}))` in an ordinary cell, verified in Excel, and its test still passes; - a **multi-cell range** is expanded only in array context — inside an array-mode function such as SUMPRODUCT, or in an array formula. In an ordinary cell Excel reduces a range criteria to the cell on the formula's own row/column (implicit intersection). `FormulaEvalTestData.xls` (Excel-generated cached values) has `SUMIF(AA7:AA13,C10:D10,C7)` on row 1364 evaluating to 1.1: the intersection fails, the criterion becomes `#VALUE!`, and SUMIF sums the one row whose AA cell *is* `#VALUE!`. Excel 365 would spill an array from that cell instead; POI's plain-cell model is the legacy one, as everywhere else in the evaluator (`A1:A3*2` in a plain cell is row-reduced too), and the two agree wherever array context exists — which is where the report's formula lives. The reporter's expectation (18) is right; the report's own case happened to pass already because of how POI "fixed" this before. ## What POI did - The `*IFS` functions (`Baseifs`, since bug 70005) evaluated once per criterion and **summed** the results, whatever the enclosing function and context: right for `SUM`/bare `SUMPRODUCT`, wrong for a weighted `SUMPRODUCT(SUMIFS(...),E1:E4)` (`#VALUE!`), `MAX(COUNTIFS(C,D))` (gave the sum, 9, instead of 2), `AVERAGE(AVERAGEIFS(...))` (summed the averages), `INDEX(SUMIFS(...),2)` (`#REF!`). - `SUMIF`/`COUNTIF`/`AVERAGEIF` reduced an array of criteria to one value even inside `SUMPRODUCT`: `SUMPRODUCT(SUMIF(C,D,B))` gave the first criterion's sum; `SUM(SUMIF(C,{1,2},B))` as an array formula gave 0. ## The fix `Countif` gains the shared pieces: `isArrayCriteria(criteria, arrayContext)` (the two rules above) and `evaluateForEachCriterion(...)`, which evaluates per element and returns a `CacheAreaEval` of the criteria's shape and position (so implicit intersection of the *result* in a plain cell picks the formula-row element, and `SUMPRODUCT`/`SUM`/`MAX`/`INDEX` see the whole array). `Countif` and `Sumif` implement `ArrayFunction` so the evaluator hands them the array context; `Baseifs` and `AverageIf` (`FreeRefFunction`s with an `OperationEvaluationContext`) get it from `Baseifs.isArrayContext(ec)` (array-mode flag from #1321, or the cell is in an array formula group). Several array criteria in one `*IFS` call are paired element-wise; different shapes give `#VALUE!`. Scalar criteria are untouched. ## Tests `TestConditionalAggregatesWithArrayCriteria` (9 tests): the report's case, plain and as an array formula; weighted `SUMPRODUCT`, `MAX`/`MIN`/`SUM`/`INDEX` over the per-criterion results; array constants across the family (bug 70005 pattern included); `SUMIF`/`COUNTIF`/`AVERAGEIF` with a range inside `SUMPRODUCT`; paired array criteria and the shape mismatch; the plain-cell implicit intersection incl. the `#VALUE!`-criterion case from the Excel file; scalar criteria unchanged. Five of them fail on trunk. `TestSumif.testCriteriaArgRange` (implicit intersection, 2009) and `TestFormulasFromSpreadsheet`/`TestFormulaEvaluatorOnXSSF` pass unchanged. Locally green: `ss.formula.*` (both modules), `ss.tests.formula.functions.*` (incl. `TestCountifs.testBug70005`), `TestHSSFFormulaEvaluator*`, `TestXSSFFormulaEvaluator*`, `TestFormulaEvaluatorBugs`, `TestBugs`, `TestFormulaEvaluatorOnXSSF`, `TestMultiSheetFormulaEvaluatorOnXSSF`, `ss.usermodel.*`. `changes.xml` left for you (bug fix, 65059). 🤖 Generated with [Claude Code](https://claude.com/claude-code) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
