PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Calculation\Exception

Must specify range of cells, not any kind of literal

Error message

Must specify range of cells, not any kind of literal

What it means

Statistical\Conditional::AVERAGEIF() requires its $range and $averageRange to be real cell-range arrays keyed by coordinate (e.g. ['A1'=>'x', ...]). If either is not an array, or is a flat list with key 0 (how literal arrays arrive from the formula parser), it throws CalcException 'Must specify range of cells, not any kind of literal'. A #REF! error value in either position is returned instead of thrown. This is a hard PHP exception, not an Excel error string, so through the engine it aborts formula evaluation.

Source

Thrown at src/PhpSpreadsheet/Calculation/Statistical/Conditional.php:44

     * Excel Function:
     *        AVERAGEIF(range,condition[, average_range])
     *
     * @param mixed $range Data values, expect array
     * @param mixed $condition the criteria that defines which cells will be checked, expect null|mixed[]|string
     * @param mixed $averageRange Data values
     */
    public static function AVERAGEIF(mixed $range, mixed $condition, mixed $averageRange = []): null|int|float|string
    {
        if ($condition !== null && !is_array($condition)) {
            $condition = StringHelper::convertToString($condition);
        }
        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);
        $condition = Functions::flattenSingleValue($condition);
        $condition = [[self::CONDITION_COLUMN_NAME, self::VALUE_COLUMN_NAME], [$condition, null]];

        return DAverage::evaluate($database, self::VALUE_COLUMN_NAME, $condition);
    }

    /**
     * AVERAGEIFS.
     *
     * Counts the number of cells that contain numbers within the list of arguments
     *
     * Excel Function:
     *        AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
     *
     * @param mixed $args Pairs of Ranges and Criteria
     */

View on GitHub (pinned to 65b080eef4)

Solutions

1. Replace the literal with an actual range reference: =AVERAGEIF(A1:A10,">1",B1:B10)
2. If the data lives in PHP, write it into cells first and reference that range in the formula
3. When calling the API directly, build the range argument the way the engine does: coordinate-keyed arrays (['A1'=>v,...]) not flat lists
4. Check for #REF! inputs first if ranges may reference deleted areas (they are returned, not thrown)

Example fix

// before: literal array -> CalcException
$sheet->getCell('D1')->setValue('=AVERAGEIF({10,20,30},">15")');

// after: values in cells, range reference used
$sheet->fromArray([[10],[20],[30]], null, 'A1');
$sheet->getCell('D1')->setValue('=AVERAGEIF(A1:A3,">15")');

// Ranges coming from the engine are coordinate-keyed maps, never flat lists
function isRangeArray(mixed $range): bool
{
    return is_array($range) && !array_key_exists(0, $range) && $range !== [];
}
if (!isRangeArray($range) || !isRangeArray($averageRange)) {
    throw new InvalidArgumentException('AVERAGEIF needs cell ranges, not literals');
}

function isCoordinateKeyedRange(mixed $r): bool
{
    if (!is_array($r) || array_key_exists(0, $r)) return false;
    foreach (array_keys($r) as $k) { if (!preg_match('/^[A-Z]{1,3}\d+$/', (string) $k)) return false; }
    return true;
}

try {
    $avg = Conditional::AVERAGEIF($range, $condition, $averageRange);
} catch (\PhpOffice\PhpSpreadsheet\Calculation\Exception $e) {
    if (str_contains($e->getMessage(), 'Must specify range of cells')) {
        throw new InvalidArgumentException('pass a real range: =AVERAGEIF(A1:A10,">1",B1:B10)');
    }
    throw $e;
}

When it happens

Trigger: =AVERAGEIF({1,2,3},">1") using an array literal; =AVERAGEIF(5,">1") with a scalar; a range reference that collapses to a single row literal during parsing; calling Conditional::AVERAGEIF([10,20],'>10') directly from PHP with a list array instead of a coordinate-keyed map.

Common situations: Converting SUMIF-style formulas to AVERAGEIF while keeping inline literals; generated formulas that substitute a PHP array or comma-joined value where a range reference is required; spreadsheets authored in other tools that export criteria functions with literal arrays; calling the Conditional class directly in tests or libraries without simulating engine-processed ranges.

Related errors

• #NUM!
• #NUM!
• #VALUE!
• #VALUE!
• #NUM!

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/b5e85aad213da9ac. Report an issue: GitHub.