SUM Function is Partially Broken

[oleibman]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

In Excel, =SUM(5.7, "x", 30) returns #VALUE. However, if A1/A2/A3 contain 5.7, x, and 30, =SUM(A1:A3) returns 35.7. With the recent re-factoring of MathTrig, both formulas evaluate to #VALUE. @MarkBaker and @PowerKiKi, I have no idea how to distinguish the 2 situations from each other in the code. It would, of course, be easy to change so that both returned 35.7, which is probably better.

What is the current behavior?

All samples using template sampleSpreadsheet.php are affected. When writing those samples as Xlsx, Xls, and Ods, they nevertheless appear okay on open because Excel etc. re-evaluate the function. However, writing those samples to Csv, Html, or Pdf will show #VALUE! when a number is expected.

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

After running the sample suite, 16_Csv.csv and 17_Html.html demonstrate this problem.

Which versions of PhpSpreadsheet and PHP are affected?

Master.

[oleibman]

I may have a solution. It won't be ready for a few hours. For a preview, and to give you the opportunity to suggest why this might not work, here's what I'm thinking:

        foreach ($args as $arg) {
            if (!is_array($arg) && !is_numeric($arg)) {
                return Functions::VALUE();
            }
        }

Add that before the main loop, and eliminate return VALUE in the main loop.

[MarkBaker]

We do actually have a mechanism to handle this, although it is a bit clumsy, and MS aren't very good at documenting which functions work this way and which don't.
In the case of SUM(): if the value "X" is a literal, then it is treated as an error in the argument list; if it is a cell value, then it is treated as valid, but with a zero value.

Rather than using the standard call to Functions::flattenArray(), we use Functions::flattenArrayIndexed() instead. The flattenArray() call returns a simple enumerated array of all the arguments; but flattenArrayIndexed() returns an associative indexed array, when the index is a reference to the worksheet/row/column. Then in the processing loop, we iterate using both key and value (foreach ($aArgs as $k => $arg))... and inside the loop we can then do a check using Functions::isCellValue($k) which will return true if the value is a cell values, and false if it is a literal; and the code logic inside the loop can then determine whether to return a VALUE! error, or process the string as a 0 value.

As an example of a function that does use this, take a look at COUNTA() in the Statistical Counts, or at the averageDeviations() method in Statistical Averages
There's even unit test cases forCOUNTA()`

[oleibman]

Thank you. I will use the COUNTA model, and expect to have have a PR, with tests, available tonight (North America west coast).