PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Calculation\Exception

#N/A

#N/A

Error message

#N/A

What it means

SumSquares::getCount() compares count($array1) with count($array2) for SUMX2MY2, SUMX2PY2 and SUMXMY2 and throws Calculation\Exception with ExcelError::NA() ('#N/A') when the two arrays differ in size, exactly like Excel. These functions pair elements positionally, so mismatched dimensions have no defined result.

Source

Thrown at src/PhpSpreadsheet/Calculation/MathTrig/SumSquares.php:46

                $arg1 = Helpers::validateNumericNullSubstitution($arg, 0);
                $returnValue += ($arg1 * $arg1);
            }
        } catch (Exception $e) {
            return $e->getMessage();
        }

        return $returnValue;
    }

    /**
     * @param mixed[] $array1
     * @param mixed[] $array2
     */
    private static function getCount(array $array1, array $array2): int
    {
        $count = count($array1);
        if ($count !== count($array2)) {
            throw new Exception(ExcelError::NA());
        }

        return $count;
    }

    /**
     * These functions accept only numeric arguments, not even strings which are numeric.
     */
    private static function numericNotString(mixed $item): bool
    {
        return is_numeric($item) && !is_string($item);
    }

    /**
     * SUMX2MY2.
     *
     * @param mixed[] $matrixData1 Matrix #1
     * @param mixed[] $matrixData2 Matrix #2

View on GitHub (pinned to 65b080eef4)

Solutions

  1. Make both ranges the same size, anchoring them to the same row span (e.g. both A2:A10 and B2:B10)
  2. If lengths legitimately differ, decide policy first: trim to the shorter with OFFSET or guard with =IF(ROWS(A2:A10)=ROWS(B2:B10), SUMXMY2(...), NA())
  3. When building ranges in PHP, derive both from one computed length ($lastRow) instead of two
  4. Catch Calculation\Exception (message '#N/A') when calling SumSquares methods directly

Example fix

// before: ranges differ by one row -> #N/A
$sheet->getCell('D1')->setValue('=SUMX2MY2(A2:A10,B2:B11)');

// after
$sheet->getCell('D1')->setValue('=SUMX2MY2(A2:A10,B2:B10)');
Defensive patterns

Strategy: validation

Validate before calling

if (count($array1) !== count($array2)) {
    throw new InvalidArgumentException(sprintf('SUMX arrays differ: %d vs %d', count($array1), count($array2)));
}

Type guard

function sameCount(array $a, array $b): bool
{
    return count($a) === count($b);
}

Try / catch

try {
    $r = SumSquares::SUMXMY2($a1, $a2);
} catch (\PhpOffice\PhpSpreadsheet\Calculation\Exception $e) {
    if ($e->getMessage() === '#N/A') { /* lengths differ */ }
}

Prevention

When it happens

Trigger: =SUMX2MY2(A1:A5,B1:B4), =SUMX2PY2(A1:A3,B1:A3-typos like B1:B4), =SUMXMY2 with ranges of different lengths; ranges where one side includes an extra header cell; direct calls SumSquares::SUMX2MY2([1,2,3],[1,2]) throwing instead of returning '#N/A'.

Common situations: Two ranges built from different tables/rows counts (actuals vs plan) that drift apart as data is appended; one range extended by a row after a copy-paste of the formula; whole-column references where one sheet has an extra populated row; dynamically generated ranges computed from separate counts.

Related errors

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/657b503c8084b031. Report an issue: GitHub.