pjfanning opened a new pull request, #1325: URL: https://github.com/apache/poi/pull/1325
Fixes https://bz.apache.org/bugzilla/show_bug.cgi?id=62271 The reporter's formula is the standard "count distinct values" idiom, `SUMPRODUCT(1/COUNTIF(A5:A10,A5:A10&""))`, and it fails on trunk with `#VALUE!` (the report predates the `Invalid arg type` → error-value change, hence the differing symptom). ### What was wrong Two steps have to work element-wise inside `SUMPRODUCT`: 1. `COUNTIF` with an array criteria must return an array of counts — done by #1323 (bug 65059). Indeed `SUMPRODUCT(1/COUNTIF(A5:A10,A5:A10))` already gives the right answer on trunk. 2. `A5:A10&""` must produce the array of texts. The arithmetic (`+ - * / ^`), comparison and unary `+`/`-` operators implement `ArrayFunction` and are evaluated element-wise in array mode, but `&` (`ConcatEval`) and `%` (`PercentEval`) were left out: the range was implicitly intersected with the formula's row, which fails for row 1, so `COUNTIF` got an error criterion, counted 0 matches and `1/0` followed. ### Fix `ConcatEval` and `PercentEval` implement `ArrayFunction` using the existing `evaluateTwoArrayArgs` / `evaluateOneArrayArg` helpers, exactly like `UnaryMinusEval`. Plain-cell behaviour is unchanged: `A5:A10&""` in an ordinary cell still implicitly intersects (legacy Excel semantics, which POI follows for plain cells). ### Excel check Excel (both legacy and 365) gives 3 for the reporter's formula over `a,b,a,c,b,a`, and 3 for the blank-tolerant variant `SUMPRODUCT((A5:A10<>"")/COUNTIF(A5:A10,A5:A10&""))` — both asserted, HSSF and XSSF, plus the `SUM(...)` array-formula form. ### Tests - `TestConcatEval` (new): basic, element-wise `evaluateArray`, spreadsheet cases incl. the reporter's formula, `SUMPRODUCT((A5:A10&""="a")*1)`, `SUMPRODUCT((A5:A10&A5:A10="bb")*1)`, array formula. - `TestPercentEval.testArrayMode`: `SUMPRODUCT(B1:B3%)`, `SUMPRODUCT(B1:B3%*B1:B3)`. - `TestXSSFBugs.testBug62271`: the reporter's case on XSSF, incl. a blank cell. 🤖 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]
