Problem with formulas (SUMIF)

Topics: Developer Forum, User Forum
Jul 28, 2010 at 7:46 PM

Hi people!

I have one big problem with formulas that i am not getting solution.

I am creating one documento .xls, and using the formula SUMIF, nevertheless, when i execute the page. Give me one error.

$horasRealizado = '=SUMIF(Realizado!$A7:$A1000, B4 ,Realizado!'.$letraColRealizado.'7:'.$letraColRealizado.'600)';
$objPHPExcel->getActiveSheet()->SetCellValue('G4', $horasRealizado);

I'm looking at some forums, I saw that problems could be put , instead ;

 But this dont fix my problem

 

Thank you very much!

I've tried everything can not know what is happening. Please, help me!

 

Jul 28, 2010 at 8:13 PM

I saw that when i put this:

$horasRealizado = '=SUMIF(A7:A1000, B4 ,Realizado!'.$letraColRealizado.'7:'.$letraColRealizado.'600)';
$objPHPExcel->getActiveSheet()->SetCellValue('G4', $horasRealizado);

without the first name of the sheet, works OK. But when i put the name of the sheet that i want to use ->>Error

Coordinator
Jul 30, 2010 at 4:49 PM

I believe this is the same problem as Slapi's problem with SUMPRODUCT http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=220058

Both reference a range in another worksheet; and searching through the code logic, it's treating the start cell of the range correctly as being in Realizado, but the end cell of the range as being in the current worksheet when it should also be in Realizado.... and this is causing conflicts in the calculation engine.

I've made a code change to the calculation engine to ensure that it picks up the correct worksheet for the end of range, and it should be available in the daily downloads tomorrow (check date to be sure you have the latest version after today). Can you please try running your script against this patched code to see if it resolves your problem.

 

Aug 2, 2010 at 8:49 PM

Thank you very much MarkBaker...

The latest version solved my problem into parts.
Really now he is solving the SUMIF, but I use it several times on my page. There comes a time when he returns to give error. I use the SUMIF,  60 times on my page.

Another problem that is happening is when I use the following formula:

=IF(G4=0,E4,IF(I4=100%,G4,IF(I4=0%,E4,G4/I4)))

It does not show anything in excel...

can you help me?

Thank you very much!!!

 

Aug 20, 2010 at 5:40 PM

Thanks Mark!

I get to solve the problem with SUMIF, the problem was because i didnt use set_time_limit, and the application broke before the excel was generated.

But the problem with formula IF continue.

When i use for example this formula:

=IF(G4=0,E4,IF(I4=100%,G4,IF(I4=0%,E4,G4/I4)))

no generate nothing.

 

 

 

Nov 20, 2010 at 8:19 AM

I have a similar problem. I want to sum up a certain range on worksheet 2 but I get the following error

Fatal error: Call to a member function cellExists() on a non-object in C:\wamp\www\MyHIS\modules\Excel2DB!\templates\data_search\Classes\PHPExcel\Calculation.php on line 3094

Below is the code I wrote:

 $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, 'Totals per age group');
 $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, '=SUM(Other Incidentals!B4 : Other Incidentals!B10)');

before writing the above code I had written the following code for the first worksheet:

 $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, 'Totals per age group');
 $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, '=SUM(B4:B25)');

and it worked

Where am I going wrong? Secondly, what file do I get from your revised document and where do I paste it? I actually copied and pasted the whole of the "Classes" folder of the download numbered: 63950 and it didn't work

Thanks

 

Coordinator
Nov 20, 2010 at 11:16 AM

Try using:

 $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, '=SUM(Other Incidentals!B4:B10)');

Also, watch out for spaces in formulae: a space is actually a special operator in Excel.

Don't try copying individual files from the SVN code, it's safer to copy the whole fileset; and specific changes may involve modifications to several files.