SUM with SUBTOTAL

[samuelgmartins]

I do not know English but would like to contribute.
Using =sum() on a reference to =subtotal() excel ignores the subtotal.
I correct with the following code:

[PTBR]
Usando =sum() em uma referencia com =subtotal() o excel ignora o subtotal.
Eu corrigir com seguinte código:

1.7.9
Calculation.php
@@ -3720,8 +3720,14 @@

sscanf($reference,'%[A-Z]%d', $currentCol, $currentRow);
$cellValue = NULL;
if ($pSheet->cellExists($reference)) {
+    $parseFormula = $this->parseFormula($pSheet->getCell($reference)->getValue());
+    
+   if(!isset($parseFormula['value']) || $parseFormula['value']!='SUBTOTAL('){
        $returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
+   } else {
+       $returnValue[$currentRow][$currentCol] = null;
+   }
} else {
    $returnValue[$currentRow][$currentCol] = NULL;
}

[matthiaz]

@samuelgmartins
I've tried out your code change, but this does not seem to fix the SUBTOTAL problem.
Tip: You might want to check out https://help.github.com/articles/creating-a-pull-request/ because this makes it a lot easier for a maintainer to review and merge your code changes.

To clarify the problem:
SUBTOTAL(9,A1:A100) should SUM all items in the range A1:A100 except the cells that contain a SUBTOTAL formula themselves.

PHPExcel does not ignore those SUBTOTALS making the grand total general twice as big. You can work around this by using setPreCalculateFormulas(false), but that isn't always a viable option.

I wouldn't classify this as an Enhancement, it looks like a bug. I tried delving into the code myself, but I'm way over my head.
My understanding of the subject is that SUBTOTAL is calculated in MathTrig. When you use SUBTOTAL(9, ... ) it means SUM.
PHPExcel processes this by linking straight to the MathTrig SUM function. So SUBTOTAL(9, is then an alias of SUM. See: https://github.com/PHPOffice/PHPExcel/blob/1.8/Classes/PHPExcel/Calculation/MathTrig.php#L1141

Unfortunatly, SUBTOTAL(9, does not work exactly the way SUM does. SUM does not ignore SUBTOTAL formulas, while SUBTOTAL itself, should.