Calculation Error "=COUNTIF(F$12:F$203,"1")"

[MiddleSiggy]

Trying to use getCalculatedValue() and the Calculation is erroring out:

PHP Code Line:
$cellValue = $sheet->getCell($column . $row)->getCalculatedValue();

In the Error State
$column = F
$row = 2

Excel Cell in F2:
=COUNTIF(F$12:F$203,"1")

Purpose of Formula in F2:

• Calculates the number of cells in the column that have a "1" in the cell (Value in my case is 18)

• The "1" is a calculated value based on the following Formula:

• =IF(ISNUMBER($E12),IF(TEXT($E12,"mmm")=F$11,"6",IF(ISNUMBER(INT(G12)),IF(VALUE(G12)=6,TEXT(INT(G12)-1,0),IF(VALUE(G12)=5,TEXT(VALUE(G12)-0.7,"0.0"),IF(G12="4.3",TEXT(VALUE(G12)-0.1,"0.0"),IF(G12="4.2",TEXT(VALUE(G12)-0.1,"0.0"),IF(G12="4.1",TEXT(VALUE(G12)-1.1,"0"),IF(G12="3",TEXT(VALUE(G12)-1,"0"),IF(G12="2",TEXT(VALUE(G12)-1,"0"),""))))))),"")),"")

• The long formula checks Column E, which is a Date value coming from the first worksheet like "=PCI!O3" (Worksheet Name PCI)

• [X ] a bug report

What is the expected behavior?

Should return the value 18

What is the current behavior?

Returns Error:
F2
PHP Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: Formula Error: An unexpected error occurred in /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:5350
Stack trace:
#0 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(4448): PhpOffice\PhpSpreadsheet\Calculation\Calculation->raiseFormulaError('Formula Error: ...')
#1 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3681): PhpOffice\PhpSpreadsheet\Calculation\Calculation->internalParseFormula('PCI!#REF!', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#2 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3468): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue('PCI!#REF!', 'E68', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#3 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(354): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue(Object(PhpOffice\PhpSpreadsheet\Cell\Cell), false)
#4 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(5387): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue(false)
#5 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(4923): PhpOffice\PhpSpreadsheet\Calculation\Calculation->extractCellRange(''Program Schedu...', Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), false)
#6 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3681): PhpOffice\PhpSpreadsheet\Calculation\Calculation->processTokenStack(Array, 'F68', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#7 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3468): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue('IF(ISNUMBER($E6...', 'F68', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#8 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(354): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue(Object(PhpOffice\PhpSpreadsheet\Cell\Cell), false)
#9 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(5397): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue(false)
#10 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(4749): PhpOffice\PhpSpreadsheet\Calculation\Calculation->extractCellRange(''Program Schedu...', Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), false)
#11 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3681): PhpOffice\PhpSpreadsheet\Calculation\Calculation->processTokenStack(Array, 'F2', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#12 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3468): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue('COUNTIF(F$12:F$...', 'F2', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#13 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(354): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue(Object(PhpOffice\PhpSpreadsheet\Cell\Cell), true)
#14 /app/splunk_spo_pci/splunk_spo_pci.php(640): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue()
#15 {main}

Next PhpOffice\PhpSpreadsheet\Calculation\Exception: Formula Error: An unexpected error occurred in /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php:3493
Stack trace:
#0 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(354): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue(Object(PhpOffice\PhpSpreadsheet\Cell\Cell), false)
#1 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(5387): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue(false)
#2 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(4923): PhpOffice\PhpSpreadsheet\Calculation\Calculation->extractCellRange(''Program Schedu...', Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), false)
#3 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3681): PhpOffice\PhpSpreadsheet\Calculation\Calculation->processTokenStack(Array, 'F68', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#4 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3468): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue('IF(ISNUMBER($E6...', 'F68', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#5 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(354): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue(Object(PhpOffice\PhpSpreadsheet\Cell\Cell), false)
#6 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(5397): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue(false)
#7 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(4749): PhpOffice\PhpSpreadsheet\Calculation\Calculation->extractCellRange(''Program Schedu...', Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), false)
#8 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3681): PhpOffice\PhpSpreadsheet\Calculation\Calculation->processTokenStack(Array, 'F2', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#9 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3468): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue('COUNTIF(F$12:F$...', 'F2', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#10 /app/lib_3rd_party/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(354): PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue(Object(PhpOffice\PhpSpreadsheet\Cell\Cell), true)
#11 /app/splunk_spo_pci/splunk_spo_pci.php(640): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue()
#12 {main}

What are the steps to reproduce?

Just run the php script, failed every time, I think it is the complicated formula?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

$spreadsheet = IOFactory::load($_workingFile);
$sheet = $spreadsheet->getSheet($_sheetIndex);
$columnsToExtract = ['E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q'];
for ($row = $_headerRow; $row <= $lastRow; $row++) 
{
$rowData = [];

foreach ($columnsToExtract as $column) 
{
echo $column . $row . "\n";
//$cellValue = $sheet->getCell($column . $row)->getValue();
$cellValue = $sheet->getCell($column . $row)->getCalculatedValue(); <--- Fails here
//$cellValue = $sheet->getCell($column . $row)->getFormattedValue();

I### What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

Only using a xlsx file, code works for extracting first worksheet, this is the second worksheet that it fails on

Which versions of PhpSpreadsheet and PHP are affected?

phpoffice/phpspreadsheet 2.0.0

[MiddleSiggy]

Seems to be working now.. Thanks

[oleibman]

Closing. Author reported it fixed 9 months ago.