Repository: PHPOffice/PhpSpreadsheet (php)

Issue:
The COUNTIF function crashes PhpSpreadsheet if there is a #REF! error in the range argument.
First of all, let it be made clear this is low-priority, such errors are correctable in-workbook to not make the application crash anymore. This bug report is made only with feature parity in mind.

### This is:

- [X] a bug report
- [ ] a feature request
- [X] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

### What is the expected behavior?

The COUNTIF function fails gracefully and returns "#REF!" too.

### What is the current behavior?

Err. 500 with message: 
```
AH01071: Got error 'PHP message: PHP Fatal error:  Uncaught TypeError: PhpOffice\\PhpSpreadsheet\\Calculation\\Statistical\\Conditional::COUNTIF(): Argument #1 ($range) must be of type array, string given in .../phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Statistical/Conditional.php:82
Stack trace:
#0 [internal function]: PhpOffice\\PhpSpreadsheet\\Calculation\\Statistical\\Conditional::COUNTIF()
#1 .../phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(5163): call_user_func_array()
#2 .../phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3743): PhpOffice\\PhpSpreadsheet\\Calculation\\Calculation->processTokenStack()
#3 .../phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php(3543): PhpOffice\\PhpSpreadsheet\\Calculation\\Calculation->_calculateFormulaValue()
#4 .../phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php(428): PhpOffice\\PhpSpreadsheet\\C...
```

### What are the steps to reproduce?

```php
$filePath='REF in COUNTIF.xlsx';

$reader = IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load($filePath);
echo $spreadsheet->getSheet(1)->getCell("A1")->getCalculatedValue();
```

Here is an Excel file to reproduce this behavior with: [REF in COUNTIF.xlsx](https://github.com/user-attachments/files/18944434/REF.in.COUNTIF.xlsx)

There are probably more issues regarding errored-out range inputs in COUNTIF.

### 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?

Not tested, but probably.

### Which versions of PhpSpreadSheet and PHP are affected?

PhpSpreadSheet: 4.0.0
PHP: 8.1

Pull request:
Handle #REF! As Argument to COUNTIF, AVERAGEIF, SUMIF
Fix #4381. The report refers to COUNTIF, but AVERAGEIF and SUMIF, which are implemented in the same module, exhibit the same behavior. (There may be others, but, for now, I will just fix those 3.)

Most methods which implement Excel functions should accept mixed arguments, so that they won't throw exceptions when calculated. Of course, MS often doesn't give much guidance as to how unexpected arguments should be handled. It at least seems clear that MS will often substitute #REF! for some arguments, and will return #REF! as the result in such cases. My test indicates that a formula using, say, #DIV/0! in lieu of #REF! will cause Excel to deem the spreadsheet corrupt. So, I think I am just going to deal with #REF! and let other unexpected values continue to throw exceptions.

This is:

- [x] a bugfix
- [ ] a new feature
- [ ] refactoring
- [ ] additional unit tests

Checklist:

- [ ] Changes are covered by unit tests
  - [ ] Changes are covered by existing unit tests
  - [x] New unit tests have been added
- [x] Code style is respected
- [x] Commit message explains **why** the change is made (see https://github.com/erlang/otp/wiki/Writing-good-commit-messages)
- [ ] CHANGELOG.md contains a short summary of the change and a link to the pull request if applicable
- [ ] Documentation is updated as necessary


Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 026a5e10b..be0581294 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -33,6 +33,7 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 ### Fixed
 
 - Refactor Helper/Html. [PR #4359](https://github.com/PHPOffice/PhpSpreadsheet/pull/4359)
+- Handle #REF! as Argument to AVERAGEIF/COUNTIF/SUMIF. [Issue #4381](https://github.com/PHPOffice/PhpSpreadsheet/issues/4381) [PR #4382](https://github.com/PHPOffice/PhpSpreadsheet/pull/4382)
 - Ignore ignoredErrors when not applicable. [Issue #4375](https://github.com/PHPOffice/PhpSpreadsheet/issues/4375) [PR #4377](https://github.com/PHPOffice/PhpSpreadsheet/pull/4377)
 - Better handling of defined names on sheets whose titles include apostrophes. [Issue #4356](https://github.com/PHPOffice/PhpSpreadsheet/issues/4356) [Issue #4362](https://github.com/PHPOffice/PhpSpreadsheet/issues/4362) [Issue #4376](https://github.com/PHPOffice/PhpSpreadsheet/issues/4376) [PR #4360](https://github.com/PHPOffice/PhpSpreadsheet/pull/4360)
 - Partial solution for removing rows or columns that include edge ranges. [Issue #1449](https://github.com/PHPOffice/PhpSpreadsheet/issues/1449) [PR #3528](https://github.com/PHPOffice/PhpSpreadsheet/pull/3528)
diff --git a/src/PhpSpreadsheet/Calculation/Statistical/Conditional.php b/src/PhpSpreadsheet/Calculation/Statistical/Conditional.php
index ae98c6080..5f75aef58 100644
--- a/src/PhpSpreadsheet/Calculation/Statistical/Conditional.php
+++ b/src/PhpSpreadsheet/Calculation/Statistical/Conditional.php
@@ -9,6 +9,7 @@ use PhpOffice\PhpSpreadsheet\Calculation\Database\DMin;
 use PhpOffice\PhpSpreadsheet\Calculation\Database\DSum;
 use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;
 use PhpOffice\PhpSpreadsheet\Calculation\Functions;
+use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
 
 class Conditional
 {
@@ -24,13 +25,18 @@ class Conditional
      * Excel Function:
      *        AVERAGEIF(range,condition[, average_range])
      *
-     * @param mixed $range Data values
+     * @param mixed $range Data values, expect array
      * @param null|array|string $condition the criteria that defines which cells will be checked
      * @param mixed $averageRange Data values
      */
     public static function AVERAGEIF(mixed $range, null|array|string $condition, mixed $averageRange = []): null|int|float|string
     {
         if (!is_array($range) || !is_array($averageRange) || array_key_exists(0, $range) || array_key_exists(0, $averageRange)) {
+            $refError = ExcelError::REF();
+            if (in_array($refError, [$range, $averageRange], true)) {
+                return $refError;
+            }
+
             throw new CalcException('Must specify range of cells, not any kind of literal');
         }
         $database = self::databaseFromRangeAndValue($range, $averageRange);
@@ -76,11 +82,21 @@ class Conditional
      * Excel Function:
      *        COUNTIF(range,condition)
      *
-     * @param mixed[] $range Data values
+     * @param mixed $range Data values, expect array
      * @param null|array|string $condition the criteria that defines which cells will be counted
      */
-    public static function COUNTIF(array $range, null|array|string $condition): string|int
+    public static function COUNTIF(mixed $range, null|array|string $condition): string|int
     {
+        if (
+            !is_array($range)
+            || array_key_exists(0, $range)
+        ) {
+            if ($range === ExcelError::REF()) {
+                return $range;
+            }
+
+            throw new CalcException('Must specify range of cells, not any kind of literal');
+        }
         // Filter out any empty values that shouldn't be included in a COUNT
         $range = array_filter(
             Functions::flattenArray($range),
@@ -169,10 +185,24 @@ class Conditional
      * Excel Function:
      *        SUMIF(range, criteria, [sum_range])
      *
-     * @param array $range Data values
+     * @param mixed $range Data values, expecting array
+     * @param mixed $sumRange Data values, expecting array
      */
-    public static function SUMIF(array $range, mixed $condition, array $sumRange = []): null|float|string
+    public static function SUMIF(mixed $range, mixed $condition, mixed $sumRange = []): null|float|string
     {
+        if (
+            !is_array($range)
+            || array_key_exists(0, $range)
+            || !is_array($sumRange)
+            || array_key_exists(0, $sumRange)
+        ) {
+            $refError = ExcelError::REF();
+            if (in_array($refError, [$range, $sumRange], true)) {
+                return $refError;
+            }
+
+            throw new CalcException('Must specify range of cells, not any kind of literal');
+        }
         $database = self::databaseFromRangeAndValue($range, $sumRange);
         $condition = [[self::CONDITION_COLUMN_NAME, self::VALUE_COLUMN_NAME], [$condition, null]];
 


Test patch:
diff --git a/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/SumIfTest.php b/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/SumIfTest.php
index 3ee886f70..397da91ad 100644
--- a/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/SumIfTest.php
+++ b/tests/PhpSpreadsheetTests/Calculation/Functions/MathTrig/SumIfTest.php
@@ -4,6 +4,7 @@ declare(strict_types=1);
 
 namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\MathTrig;
 
+use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;
 use PHPUnit\Framework\Attributes\DataProvider;
 
 class SumIfTest extends AllSetupTeardown
@@ -38,4 +39,22 @@ class SumIfTest extends AllSetupTeardown
     {
         return require 'tests/data/Calculation/MathTrig/SUMIF.php';
     }
+
+    public function testOutliers(): void
+    {
+        $sheet = $this->getSheet();
+        $sheet->getCell('A1')->setValue('=SUMIF(5,"<32")');
+
+        try {
+            $sheet->getCell('A1')->getCalculatedValue();
+            self::fail('Should receive exception for non-array arg');
+        } catch (CalcException $e) {
+            self::assertStringContainsString('Must specify range of cells', $e->getMessage());
+        }
+
+        $sheet->getCell('A4')->setValue('=SUMIF(#REF!,"<32")');
+        self::assertSame('#REF!', $sheet->getCell('A4')->getCalculatedValue());
+        $sheet->getCell('A5')->setValue('=SUMIF(D1:D4, 1, #REF!)');
+        self::assertSame('#REF!', $sheet->getCell('A5')->getCalculatedValue());
+    }
 }
diff --git a/tests/PhpSpreadsheetTests/Calculation/Functions/Statistical/AverageIfTest.php b/tests/PhpSpreadsheetTests/Calculation/Functions/Statistical/AverageIfTest.php
index c25b636b8..a19f0736d 100644
--- a/tests/PhpSpreadsheetTests/Calculation/Functions/Statistical/AverageIfTest.php
+++ b/tests/PhpSpreadsheetTests/Calculation/Functions/Statistical/AverageIfTest.php
@@ -5,10 +5,11 @@ declare(strict_types=1);
 namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\Statistical;
 
 use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;
+use PHPUnit\Framework\Attributes\DataProvider;
 
 class AverageIfTest extends AllSetupTeardown
 {
-    #[\PHPUnit\Framework\Attributes\DataProvider('providerAVERAGEIF')]
+    #[DataProvider('providerAVERAGEIF')]
     public function testAVERAGEIF(mixed $expectedResult, mixed ...$args): void
     {
         $this->runTestCaseNoBracket('AVERAGEIF', $expectedResult, ...$args);
@@ -41,5 +42,11 @@ class AverageIfTest extends AllSetupTeardown
         }
         $sheet->getCell('A3')->setValue('=AVERAGEIF(C1:C1,"<32")');
         self::assertSame(5, $sheet->getCell('A3')->getCalculatedValue(), 'first arg is single cell');
+
+        $sheet->getCell('A4')->setValue('=AVERAGEIF(#REF!,1)');
+        self::assertSame('#REF!', $sheet->getCell('A4')->getCalculatedValue());
+
+        $sheet->getCell('A5')->setValue('=AVERAGEIF(D1:D4, 1, #REF!)');
+        self::assertSame('#REF!', $sheet->getCell('A5')->getCalculatedValue());
     }
 }
diff --git a/tests/PhpSpreadsheetTests/Calculation/Functions/Statistical/CountIfTest.php b/tests/PhpSpreadsheetTests/Calculation/Functions/Statistical/CountIfTest.php
index a4e803138..7193b8ae3 100644
--- a/tests/PhpSpreadsheetTests/Calculation/Functions/Statistical/CountIfTest.php
+++ b/tests/PhpSpreadsheetTests/Calculation/Functions/Statistical/CountIfTest.php
@@ -4,9 +4,12 @@ declare(strict_types=1);
 
 namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\Statistical;
 
+use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;
+use PHPUnit\Framework\Attributes\DataProvider;
+
 class CountIfTest extends AllSetupTeardown
 {
-    #[\PHPUnit\Framework\Attributes\DataProvider('providerCOUNTIF')]
+    #[DataProvider('providerCOUNTIF')]
     public function testCOUNTIF(mixed $expectedResult, mixed ...$args): void
     {
         $this->runTestCaseNoBracket('COUNTIF', $expectedResult, ...$args);
@@ -27,4 +30,20 @@ class CountIfTest extends AllSetupTeardown
     {
         return require 'tests/data/Calculation/Statistical/COUNTIF.php';
     }
+
+    public function testOutliers(): void
+    {
+        $sheet = $this->getSheet();
+        $sheet->getCell('A1')->setValue('=COUNTIF(5,"<32")');
+
+        try {
+            $sheet->getCell('A1')->getCalculatedValue();
+            self::fail('Should receive exception for non-array arg');
+        } catch (CalcException $e) {
+            self::assertStringContainsString('Must specify range of cells', $e->getMessage());
+        }
+
+        $sheet->getCell('A4')->setValue('=COUNTIF(#REF!,1)');
+        self::assertSame('#REF!', $sheet->getCell('A4')->getCalculatedValue());
+    }
 }