Problem parsing SUMIF function?

Topics: Developer Forum
Dec 16, 2011 at 9:12 PM

First, I really love PHPExcel.  It is a great utility and I'm very pleased with it!  I'm using the latest release, 1.7.6.

I am having a bit of a problem reading an Excel file, and specifically with the SUMIF() function, when the arguments are not in the current sheet.

Here is the function from Excel:

=SUMIF('YoY Actual (New)'!$C:$C,'Vol vs. Goal'!$C35,'YoY Actual (New)'!H:H)

'YoY Actual (New)' is another sheet in the spreadsheet.  'Vol vs. Goal' is the current sheet.

What I'm trying to do is get the calculated value, like this:

$d = $sheet->getCell('D35')->getCalculatedValue();

I get the following in my PHP log:

[16-Dec-2011 11:24:16] PHP Fatal error:  Uncaught exception 'Exception' with message 'Vol vs. Goal!D35 -> Formula Error: Wrong number of arguments for SUMIF() function: 1 given, either 2 or 3 expected' in E:\PHPBatch\PHPExcel\Cell.php:284
Stack trace:
#0 E:\PHPBatch\LoadSales.php(534): PHPExcel_Cell->getCalculatedValue()
#1 {main}
  thrown in E:\PHPBatch\PHPExcel\Cell.php on line 284

Note that it thinks I only passed one parameter to SUMIF() instead of 2 or 3.  Looking at the cell, I do have 3 parameters.

I added a bunch of print statements into Calculation.php to dump into my application log and got this:

2011-12-16 15:58:46    Cell value for D35 is a formula: Calculating value.
2011-12-16 15:58:46    Testing cache value
2011-12-16 15:58:46    Parsing Formula SUMIF('YoY Actual (New)'!$C:$C,'Vol vs. Goal'!$C35,'YoY Actual (New)'!H:H).
2011-12-16 15:58:46    Assessing Expression SUMIF('YoY Actual (New)'!$C:$C,'Vol vs. Goal'!$C35,'YoY Actual (New)'!H:H)
2011-12-16 15:58:46    Initial character of expression block is S
2011-12-16 15:58:46    $isOperandOrFunction is True
2011-12-16 15:58:46    Element with value SUMIF( is an Operand, Variable, Constant, String, Number, Cell Reference or Function
2011-12-16 15:58:46    Element SUMIF( is a Function
2011-12-16 15:58:46    Assessing Expression 'YoY Actual (New)'!$C:$C,'Vol vs. Goal'!$C35,'YoY Actual (New)'!H:H)
2011-12-16 15:58:46    Initial character of expression block is '
2011-12-16 15:58:46    $isOperandOrFunction is True
2011-12-16 15:58:46    Element with value 'YoY Actual (New)'!$C:$C,'Vol vs. Goal'!$C35 is an Operand, Variable, Constant, String, Number, Cell Reference or Function
2011-12-16 15:58:46    Element 'YoY Actual (New)'!$C:$C,'Vol vs. Goal'!$C35 is a Cell reference
2011-12-16 15:58:46    Assessing Expression ,'YoY Actual (New)'!H:H)
2011-12-16 15:58:46    Initial character of expression block is ,
2011-12-16 15:58:46    $isOperandOrFunction is True
2011-12-16 15:58:46    Element with value , is an Operator.
2011-12-16 15:58:46    Assessing Expression 'YoY Actual (New)'!H:H)
2011-12-16 15:58:46    Initial character of expression block is '
2011-12-16 15:58:46    $isOperandOrFunction is True
2011-12-16 15:58:46    Element with value 'YoY Actual (New)'!H is an Operand, Variable, Constant, String, Number, Cell Reference or Function
2011-12-16 15:58:46    Element is a Variable, Constant, String, Number, Boolean
2011-12-16 15:58:46    Assessing Expression :H)
2011-12-16 15:58:46    Initial character of expression block is :
2011-12-16 15:58:46    $isOperandOrFunction is False
2011-12-16 15:58:46    Element with value : is an Operator.
2011-12-16 15:58:46    Assessing Expression H)
2011-12-16 15:58:46    Initial character of expression block is H
2011-12-16 15:58:46    $isOperandOrFunction is True
2011-12-16 15:58:46    Element with value H is an Operand, Variable, Constant, String, Number, Cell Reference or Function
2011-12-16 15:58:46    Element is a Variable, Constant, String, Number, Boolean
2011-12-16 15:58:46    Assessing Expression )
2011-12-16 15:58:46    Initial character of expression block is )
2011-12-16 15:58:46    $isOperandOrFunction is False
2011-12-16 15:58:46    Element is a closing bracket
2011-12-16 15:58:46    Closed Function is SUMIF.
2011-12-16 15:58:46    argMatch:
array (
  0 => '2,3',
  1 => '2',
  2 => ',',
  3 => '3',
)
2011-12-16 15:58:46    Calculation Exception: Formula Error: Wrong number of arguments for SUMIF() function: 1 given, either 2 or 3 expected.

Thanks.

Dec 20, 2011 at 2:15 PM

This is a critical issue for me, and I'm not able to figure out how to fix the code.  Can someone please help?

Thanks.

Jan 11, 2012 at 2:43 PM

Please, has anyone seen this?  Does anyone have a solution?  Functions that reference other sheets in the spreadsheet are NOT parsing correctly for me.  Am I doing something wrong?

 

HELP??

Jan 11, 2012 at 2:49 PM

If i'm not mistaken, it is not supported

Jan 11, 2012 at 3:54 PM

Oh darn.  I was hoping it was something I was doing wrong.  Any chance of a work-around?  What are other people doing in this situation?  I'm hoping to avoid writing my own code to handle it.

Jan 17, 2012 at 9:15 AM
Edited Jan 17, 2012 at 9:33 AM

So you can't use array formulas (CSE formulas) and you can't reference cells in other sheets with "=sumif" or "=sumifs" in PHPExcel.  How are you supposed to conditionally aggregate information on another sheet?  I feel like I'm scrapping the bottom of the barrel of my Excel tricks just to get a simple report that will sum all the shipped quantities on a given day.

EDIT:

As for your specific case, could you try to copy and pastevalue.  I'm pretty new to PHPExcel, but that's how I might try to hack something together in VBA.

Coordinator
Jan 17, 2012 at 12:06 PM
Edited Jan 17, 2012 at 12:07 PM

References to other worksheets should work correctly in calculations, and array formulas are partially supported.

Try setting

PHPExcel_Calculation::setArrayReturnType(PHPExcel_Calculation::RETURN_ARRAY_AS_ARRAY);

before calling getCalculatedValue()

If it doesn't work, try with the latest SVN code: failing that, then I need to investigate on a case-by-case basis, with specific test data.

Jan 22, 2013 at 10:42 AM
Edited Jan 22, 2013 at 10:42 AM

I'm running into the same problem. By trying to get the value of a SUMIFS formula I get 0 everytime when I use getCalculatedValue. I've tried it within an active worksheet and read the file again, but always the same problem. Is there any solution?

Coordinator
Jan 22, 2013 at 10:57 AM

As per the documentation:

SUMIFS                  CATEGORY_MATH_AND_TRIG          ***  Not yet Implemented

which means that getCalculatedValue() cannot yet calculate the result of SUMIFS() functions in your worksheets

Jan 22, 2013 at 11:10 AM

Are there any alternatives to do this?