{"record":{"id":"657b503c8084b031","repo":"PHPOffice/PhpSpreadsheet","slug":"n-a-657b50","errorCode":"#N/A","errorMessage":"#N/A","messagePattern":"#N/A","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/MathTrig/SumSquares.php","lineNumber":46,"sourceCode":"                $arg1 = Helpers::validateNumericNullSubstitution($arg, 0);\n                $returnValue += ($arg1 * $arg1);\n            }\n        } catch (Exception $e) {\n            return $e->getMessage();\n        }\n\n        return $returnValue;\n    }\n\n    /**\n     * @param mixed[] $array1\n     * @param mixed[] $array2\n     */\n    private static function getCount(array $array1, array $array2): int\n    {\n        $count = count($array1);\n        if ($count !== count($array2)) {\n            throw new Exception(ExcelError::NA());\n        }\n\n        return $count;\n    }\n\n    /**\n     * These functions accept only numeric arguments, not even strings which are numeric.\n     */\n    private static function numericNotString(mixed $item): bool\n    {\n        return is_numeric($item) && !is_string($item);\n    }\n\n    /**\n     * SUMX2MY2.\n     *\n     * @param mixed[] $matrixData1 Matrix #1\n     * @param mixed[] $matrixData2 Matrix #2","sourceCodeStart":28,"sourceCodeEnd":64,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/MathTrig/SumSquares.php#L28-L64","documentation":"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.","triggerScenarios":"=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'.","commonSituations":"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.","solutions":["Make both ranges the same size, anchoring them to the same row span (e.g. both A2:A10 and B2:B10)","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())","When building ranges in PHP, derive both from one computed length ($lastRow) instead of two","Catch Calculation\\Exception (message '#N/A') when calling SumSquares methods directly"],"exampleFix":"// before: ranges differ by one row -> #N/A\n$sheet->getCell('D1')->setValue('=SUMX2MY2(A2:A10,B2:B11)');\n\n// after\n$sheet->getCell('D1')->setValue('=SUMX2MY2(A2:A10,B2:B10)');","handlingStrategy":"validation","validationCode":"if (count($array1) !== count($array2)) {\n    throw new InvalidArgumentException(sprintf('SUMX arrays differ: %d vs %d', count($array1), count($array2)));\n}","typeGuard":"function sameCount(array $a, array $b): bool\n{\n    return count($a) === count($b);\n}","tryCatchPattern":"try {\n    $r = SumSquares::SUMXMY2($a1, $a2);\n} catch (\\PhpOffice\\PhpSpreadsheet\\Calculation\\Exception $e) {\n    if ($e->getMessage() === '#N/A') { /* lengths differ */ }\n}","preventionTips":["Derive both ranges from one computed $lastRow when generating formulas","Use identical anchors (both start row 2, same height) for the paired ranges","After appending data, re-check ROWS(x)=ROWS(y) for copied SUMX formulas","Trim to the shorter side deliberately (OFFSET/FILTER) when sizes legitimately differ"],"tags":["phpspreadsheet","excel-formula","math","sumx2my2","sumxmy2","array-mismatch","na-error"],"backgroundTag":"array-size-mismatch","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}