PHPExcel SUMIF返回错误/未计算的公式

Question

I have a cell C13 with a simple test formula =SUMIF(D8:D18,2,E8:E18). D8->D18 Holds 2 2's and the values of D8:D18 are hardcoded, not retrieved from another formula. The range of E8->E18 are all 1's. In excel the correct value is 2. But when i call PHPExcel with the following simple code.

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
PHPExcel_Calculation::getInstance($objPHPExcel)->clearCalculationCache();
$objReader  = $objPHPExcel->setActiveSheetIndexByName("TestSumIf");
$value = $objPHPExcel->getActiveSheet()->getCell('C13')->getCalculatedValue();

I get $value = 0.

Formula Value is=SUMIF(D8:D18,2,E8:E18)

Expected Value is 0

Parser Stack :- Array ( [0] => Array ( [type] => Cell Reference [value] => D8 [reference] => D8 ) [1] => Array ( [type] => Cell Reference [value] => D18 [reference] => D18 ) [2] => Array ( [type] => Binary Operator [value] => : [reference] => ) [3] => Array ( [type] => Value [value] => 2 [reference] => ) [4] => Array ( [type] => Cell Reference [value] => E8 [reference] => E8 ) [5] => Array ( [type] => Cell Reference [value] => E18 [reference] => E18 ) [6] => Array ( [type] => Binary Operator [value] => : [reference] => ) [7] => Array ( [type] => Operand Count for Function SUMIF() [value] => 3 [reference] => ) [8] => Array ( [type] => Function [value] => SUMIF( [reference] => ) )

Calculated Value is 0

Evaluation Log:

Anyone have an insight as to why this might be happening. Ive seen the documentation saying there should be no error with SUMIF but only with SUMIFS that can be rewritten as SUMPRODUCT, but in my case SUMIF is causing the error.

Answer 1

Well, if this causes a problem for anyone. What i personally did was changed the SUMIF function in MathTrig.php to the following. In my case i want to exclude values that are '---' in my $array and $sum_array. It also counts the new array $ar count if the values are equal to those in $checkVars. Change the if statement to your own specifications.

public static function SUMIF($array,$criteria,$sum_array)
{ 
 $array = PHPExcel_Calculation_Functions::flattenArray($array);
 $sum_array = PHPExcel_Calculation_Functions::flattenArray($sum_array);
 $arraySize = sizeof($array);
 $arraySizeCount = 0;
 $checkVars = array(18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75);
    for($i = 0; $i < $arraySize;$i++)
    {
        if($array[$i] === '---')
        {
            unset($array[$i]);
            unset($sum_array[$i]);
        }
        else if(in_array($array[$i], $checkVars))
        {
            $arraySizeCount++;
        }
    }
 if(is_array($array) && is_array($sum_array) && trim($criteria)!="")
 { 
    $result = 0 ;
        for($i=0;$i<$arraySizeCount;$i++)
        {
            if(preg_match("/^</",$criteria))
            {
                $value = preg_replace("/^</","",$criteria);
                $result += $array[$i] < $value ? $sum_array[$i]:0; 
             }
            elseif(preg_match("/^>/",$criteria))
            { 
                $value = preg_replace("/^>/","",$criteria);
                $result += $array[$i] > $value ? $sum_array[$i]:0;
            }
            else
            {
                $value = $criteria;
                $result += $array[$i] == $value ? $sum_array[$i]:0;
                echo $result; 
            } 
        }
    return $result ? $result:0;
 }
}

It works perfectly. If anyone has a mean of writing this better, please let me know.