[FIXED] Why can't I add sumproduct formula?

Issue

I tried to add a column named FTE containing this formula :

=I2/SUMPRODUCT(I:I,(M:M=M2) * (C:C=C2) * (N:N=N2))

This formula worked when applied in Excel, but when added from php, I just get an error:

maximum execution time calculation.php phpexcel

$assembly = $arrayWorksheet->addColValByRow($assembly, 'FTE', ['= I', '/SUMPRODUCT(I:I,(M:M=M', ')*(C:C=C', ')*(N:N=N'], '))');
// Output : =I2/SUMPRODUCT(I:I,(M:M=M2)*(C:C=C2)*(N:N=N2))

Sadly I can't figure out why. My initial formula was SUMIFS that I converted into SUMPRODUCT, because I know that SUMIFS isn't implemented yet.

function addColValByRow($worksheet, $title, $valArray, $finalVal = NULL) {
        // Take the last column
        $lastCol = key( array_slice( $worksheet[1], -1, 1, TRUE ) );
        $newCol = ++$lastCol;
        // tke the last cell
        $lastCell = key( array_slice( $worksheet, -1, 1, TRUE ) );
        $worksheet[1][$newCol] = $title;

        for ($i = 2; $i <= $lastCell; $i++) {
            $worksheet[$i][$newCol] = "";
            foreach (array_keys($valArray) as $key) {
                $worksheet[$i][$newCol] .= $valArray[$key] . $i;
            }
            if ($finalVal != NULL) {
                $worksheet[$i][$newCol] .= $finalVal;
            }
        }

        return $worksheet;
    }

Solution

It's not SUMPRODUCT() that's causing the problem, it's the fact that PHPExcel doesn't support column/row ranges fully, so it's a column range like I:I or M:M or C:C that's causing the problem.

If you can change this to an actual cell range (e.g. I1:I2048), then it shouldn't be an issue.

And (for future reference) SUMIFS() is implemented in the latest code in the develop branch on github

Answered By - Mark Baker

Answer Checked By - Clifford M. (PHPFixing Volunteer)