Inconsistent results when using COUNTIF

[roland-jungwirth]

This is:

- [x] a bug report

What is the expected behavior?

When using COUNTIF in a cell (with a numerical condition), I expect the result to be the same when I reference the cell and use the getCalculatedValue() function.

What is the current behavior?

The range B1:B8 has 6 numbers, on cell with text and one empty cell: =COUNTIF(B1:B8;">0") in Excel (cell "D2") returns 6 $spreadsheet->getActiveSheet()->getCell('D2')->getCalculatedValue() returns 7, as it includes the text cell.

What are the steps to reproduce?

<?php

require __DIR__ . '/vendor/autoload.php';

          $template_file = 'countif.xlsx';
          $filename = 'test_' . date( 'Y-m-d_H-i-s' ) . '.xlsx';

          // Load the template file
          $reader = IOFactory::createReader('Xlsx');
          $reader->setIncludeCharts(TRUE);

          $spreadsheet = $reader->load($template_file);

          $spreadsheet->getActiveSheet()->setCellValue(
                  'D6',
                  $spreadsheet->getActiveSheet()->getCell('D2')->getCalculatedValue()
          );

          // Set the headers to force a download of the Excel file
          header( 'Content-Type: application/vnd.ms-excel' );
          header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
          header( 'Cache-Control: max-age=0' );

          // Create a writer and send the Excel file to the browser
          $writer = new Xlsx($spreadsheet);

          ob_end_clean();
          $writer->setIncludeCharts(TRUE);
          $writer->save( 'php://output' );
          exit;

Here is the example Excel file I use countif.xlsx

What features do you think are causing the issue

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

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

Which versions of PhpSpreadsheet and PHP are affected?

Probably all, but I am using PHP 8.1 and PhpSpreadsheet 1.29.

[oleibman]

Ugh! There are many descriptions of COUNTIF on the web, including Microsoft's own, and none of them describe this behavior. Further, while they all have lists of gotchas, none of them mention this. Excel evaluates =IF("something" > 0, TRUE, FALSE) as TRUE, yet doesn't count it for COUNTIF. Complicating any fix, the PhpSpreadsheet code for evaluating COUNTIF is shared by several other Excel functions, and I don't know that this particular wrinkle applies to all of them. So a fix may take a while.

[roland-jungwirth]

It doesn't even point this out on Microsoft's own page: https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34