How to sum a cell over multiple sheets

Topics: Developer Forum
Jan 22, 2013 at 11:02 PM
Edited Jan 22, 2013 at 11:17 PM

I've been trying for hours to make a formula that will sum the same cell in multiple sheets.  It should look like this:

=SUM(sheet_0:sheet_1!D13)

However this:
$worksheet->setCellValue('D10', '=SUM(sheet_0:sheet_1!D13)');

Gives me the error:
Fatal error:  Call to a member function cellExists() on a non-object in ...\PHPExcel\Calculation.php on line 3241

If I manually create a workbook in excel I can enter in that formula and it works.

I'm using setTitle() to name each worksheet.  Is there a way to do this without names perhaps?

I'm using PHPExcel v1.7.8.

<b>Fatal error</b>:  Call to a member function cellExists() on a non-object in <b>C:\Work\Clients\Elite Meetings\admin.elitemeetings.com\git\library\PHPExcel\Calculation.php</b> on line <b>3241</b><br />
Coordinator
Jan 22, 2013 at 11:41 PM

3D ranges aren't supported by the calculation engine. If you want to save a worksheet with a formula like this, then Excel2007 is the only option, and only if preCalculateFormulas is set to FALSE

Jan 23, 2013 at 12:15 AM
Edited Jan 23, 2013 at 12:16 AM

Ah, thanks!  That was quite helpful.

For anyone else reading this, I used the following:

$worksheet->setCellValue('D10', "=SUM('sheet_0:sheet_1'!D13)");

Some of my sheet names have spaces, thus they are surrounded by 'single-quotes'

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->setPreCalculateFormulas(false);

Where $excel is my PHPExcel object:
$excel = new PHPExcel();

May 29, 2014 at 2:32 PM
Very Helpfull!

But, what if I have to do this for all the "summary sheet" cells and not only for 1 cell?

For example: if every cells in D column in my "summary sheet" is the sum of every corrispondent D-Column-cell of all other sheets (and I can't predict how many sheets I have)?