Incorrect calculated value for SUMPRODUCT formula with multi-row named range

[DigitalFeonix]

This is:

- [x] a bug report

What is the expected behavior?

When calculating the value of a formula field that it would match what Excel shows.

What is the current behavior?

Using this spreadsheet as an example:

Using the following named rages:

• calHours => =Sheet1!$B$3:$G$9
• calNames => =Sheet1!$B$1:$G$1
• calSums => =Sheet1!$B$10:$G$10
• calTiers => =Sheet1!$B$2:$G$2

When calculating a SUMPRODUCT formula like =SUMPRODUCT(((calNames=I3)*(calTiers=$K$2))*calHours) where calHours is a multi-row range the value is incorrect. If the named range calHours is replaced with calSums which is a single-row range it will calculate correctly.

Version 1.28.0 and higher will return the following values:

• K3 = 14
• K4 = 16
• K5 = 12

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:

<?php

require __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

$input = './minimum.xlsx';

// add code that show the issue here...

try {

    $reader = new Xlsx();
    $spreadsheet = $reader->load($input);
    $spreadsheet->setActiveSheetIndexByName('Sheet1');

    $spreadsheet->getCalculationEngine()->getDebugLog()->setWriteDebugLog(true);

    $active_sheet = $spreadsheet->getActiveSheet();

    echo 'Multi-line Matrix',"\n";
    echo 'Alice = ', $active_sheet->getCell('K3')->getCalculatedValue(), "\n";

    $log = $spreadsheet->getCalculationEngine()->getDebugLog()->getLog();

    echo 'Bob = ', $active_sheet->getCell('K4')->getCalculatedValue(), "\n";
    echo 'Charlie = ', $active_sheet->getCell('K5')->getCalculatedValue(), "\n";
    echo "\n";

    echo 'Single-line Matrix',"\n";
    echo 'Alice = ', $active_sheet->getCell('M3')->getCalculatedValue(), "\n";
    echo 'Bob = ', $active_sheet->getCell('M4')->getCalculatedValue(), "\n";
    echo 'Charlie = ', $active_sheet->getCell('M5')->getCalculatedValue(), "\n";

    file_put_contents(sprintf('spreadsheet-%s.log', date('Ymdhis')), print_r($log, true));

} catch (Exception $e) {
    echo 'Caught error....', $e->getMessage(), "\n";
}

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

minimum.xlsx

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

Haven't tested, but confirmed that xlsm and xslx are affected.

Which versions of PhpSpreadsheet and PHP are affected?

Version 1.26.0 is last version that would correctly calculate values for multi-row and single-row ranges.
Version 1.27.x regressed to calculating 0 as the value for both multi-row and single-row ranges.
Version 1.28.0 and higher calculate incorrect values for multi-row ranges

Tested with PHP 8.0.30, 8.1.27, 8.2.14, and 8.3.1 and all gave same results.