Subtotal Calculation

[cloelke]

This is:

• [x ] a bug report

What is the expected behavior?

Subtotal 9 in a group that has other subtotal 9 should excluded the totals of the other subtotals in the range

What is the current behavior?

Subtotal does not skip other subtotals and includes it like a sum

What are the steps to reproduce?

If you run the below then delete A8 in excel then undo. Excel with provide the correct total of 4
the below is producing 6

<?php

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

// Create new Spreadsheet object
$objPhpSpreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$objPhpSpreadsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 2, 1);
$objPhpSpreadsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 3, 1);
$objPhpSpreadsheet->getActiveSheet()->setCellValue('A4','=SUBTOTAL(9,A2:A3)');

$objPhpSpreadsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 6, 1);
$objPhpSpreadsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 7, 1);
$objPhpSpreadsheet->getActiveSheet()->setCellValue('A8','=SUBTOTAL(9,A2:A7)');


$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($objPhpSpreadsheet, 'Xlsx');
$objWriter->save('php://output');

Which versions of PhpSpreadsheet and PHP are affected?

[PowerKiKi]

I can confirm both LibreOffice 5 and Excel 2016 will produce 4, but PhpSpreadsheet will produce 6 with the following, essentially same, code:

<?php

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

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A2', 1);
$sheet->setCellValue('A3', 1);
$sheet->setCellValue('A4', '=SUBTOTAL(9,A2:A3)');

$sheet->setCellValue('A6', 1);
$sheet->setCellValue('A7', 1);
$sheet->setCellValue('A8', '=SUBTOTAL(9,A2:A7)');

var_dump($sheet->getCell('A8')->getCalculatedValue());

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[karlcampbell]

I'm also having the same problem. Excel 16 on Mac will display as a sum until I recalculate the formula. Has anyone figured this out?

[maerni]

Guten Tag Unsere Firma ist bis zum 23. April 2018 ferienhalber geschlossen. Die Nachrichten werden in dieser Zeit nur sporadisch gelesen. In dringenden Fällen füllen Sie bitte unser Supportformular aus: https://support.yourchoice.ch/ Mit freundlichen Grüssen YourChoice Informatik GmbH

…

________________________________ YourChoice Informatik GmbH Mattenweg 15 2557 Studen BE [email protected] https://www.yourchoice.ch

[karlcampbell]

I attempted to update the SUBTOTAL function. I noticed there was a filter to remove hidden cells. So I copied it to remove cells that contain =SUBTOTAL( string. Does this make sense? It seems to work, does anyone see any issues with this?

protected static function filterFormulaArgs($cellReference, $args)
    {
        return array_filter(
            $args,
            function ($index) use ($cellReference) {
                list(, $row, $column) = explode('.', $index);

                //take this cell out if it contains the SUBTOTAL formula
                return strpos(strtoupper($cellReference->getWorksheet()->getCell($column . $row)->getValue()), '=SUBTOTAL(') === false;

            },
            ARRAY_FILTER_USE_KEY
        );
    }

    /**
     * SUBTOTAL.
     *
     * Returns a subtotal in a list or database.
     *
     * @param int the number 1 to 11 that specifies which function to
     *                    use in calculating subtotals within a list
     * @param array of mixed Data Series
     *
     * @return float
     */
    public static function SUBTOTAL(...$args)
    {
        $aArgs = Functions::flattenArrayIndexed($args);
        $cellReference = array_pop($aArgs);
        $subtotal = array_shift($aArgs);

        // Calculate
        if ((is_numeric($subtotal)) && (!is_string($subtotal))) {
            if ($subtotal > 100) {
                $aArgs = self::filterHiddenArgs($cellReference, $aArgs);
                $subtotal = $subtotal - 100;
            }

            switch ($subtotal) {
                case 1:
                    return Statistical::AVERAGE($aArgs);
                case 2:
                    return Statistical::COUNT($aArgs);
                case 3:
                    return Statistical::COUNTA($aArgs);
                case 4:
                    return Statistical::MAX($aArgs);
                case 5:
                    return Statistical::MIN($aArgs);
                case 6:
                    return self::PRODUCT($aArgs);
                case 7:
                    return Statistical::STDEV($aArgs);
                case 8:
                    return Statistical::STDEVP($aArgs);
                case 9:
                    //remove cells that contain the SUBTOTAL formula that we're going to pass to SUM
                    $aArgs = self::filterFormulaArgs($cellReference, $aArgs);
                    return self::SUM($aArgs);
                case 10:
                    return Statistical::VARFunc($aArgs);
                case 11:
                    return Statistical::VARP($aArgs);
            }
        }

        return Functions::VALUE();
    }

[MarkBaker]

Looking at how this is working in MS Excel,

self::filterFormulaArgs($cellReference, $aArgs);

Needs to apply to all subtotal types, not simply to type 9

[MarkBaker]

Implemented code change on branch issue-332, will create unit tests later (probably over this weekend)

[MarkBaker]

Resolved on develop branch