"3D Range references are not yet supported" error when trying to use GROUPBY, is there a way to support it?

[cuartas15]

This is:

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

What is the expected behavior?

That a =GROUPBY formula works

What is the current behavior?

It doesn't work, throwing the error "3D Range references are not yet supported"

What are the steps to reproduce?

Have a table with dates and values, in a different sheet try to group them by dates and values so it sums the total on each date, like this: $workSheet2->setCellValue('A1', '=GROUPBY(Report!B2:B' . (count($data) + 1) . ';Report!D2:D' . (count($data) + 1) . ';SUM)');

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:

<?php
$spreadsheet = new Spreadsheet();
$workSheet = $spreadsheet->getSheet(0);
$workSheet->setTitle('Report');

$data = array(
    ['anything', '2024-12-10', 'anything', '5000'],
    ['anything', '2024-12-10', 'anything', '2500'],
    ['anything', '2024-12-09', 'anything', '12000'],
    ['anything', '2024-12-09', 'anything', '100'],
);

$workSheet->fromArray($data, '', 'A2');

$workSheet2 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Dynamic');
$spreadsheet->addSheet($workSheet2);
$workSheet2->setTitle('Dynamic table');

$workSheet2->setCellValue('A1', '=GROUPBY(Report!B2:B' . (count($data) + 1) . ';Report!D2:D' . (count($data) + 1) . ';SUM)');
?>

What features do you think are causing the issue

• [ ] Reader
• [x] 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?

At least xlsx

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 1.29.6 PHP 7.4

[oleibman]

I am not aware of any imminent plans to support GROUPBY.

However, I am puzzled by your "3D Range references". I run your code and don't see such a message. And I don't see any 3D references in your code. Is there a section of code that you have not provided?

[oleibman]

Ah, yes, when I try to save the file, I see the 3D message. I'll take a look.

[oleibman]

When I change the semi-colons in your formula to commas, the 3D message goes away.

[cuartas15]

When I change the semi-colons in your formula to commas, the 3D message goes away.

Yeah I remember trying it with commas yesterday but the file wouldn't just open, excel would say the file is broken. But in any case, by your comments, it's confirmed GROUPBY isn't supported by the library at all?

[oleibman]

When I save, Excel does not object to the result, but the GROUPBY cells aren't particularly useful. I can put support for GROUPBY on my to-do list, but I'm sure it will take me a while before I can get to it. Apart from implementing the function, Excel has added a whole new means of tagging the third (function) argument, e.g. the formula stored in the XML might be:

<f t="array" ref="E3:F8">_xlfn.GROUPBY(B2:B32, C2:C32, _xleta.SUM, 3)</f>

The _xlfn part we're long used to, but _xleta is new and potentially difficult.

[oleibman]

PR #4283 will provide some relief for you. In particular, note https://github.com/PHPOffice/PhpSpreadsheet/pull/4283#issuecomment-2554807700. It's far from ideal, but it may help.

[jsandeo]

When I change the semi-colons in your formula to commas, the 3D message goes away.

This solved a bug (unrelated to GROUPBY) where I was having the same error "3D Range references are not yet supported" after upgrading PhpSpreadsheet. Looks like previous versions accepted semi-colons as argument separators in formulas, but this is no longer the case.

[oleibman]

Thanks for letting us know. We strive to avoid it, but still we sometimes have unexpected changes when we merge new code. It's nice to know that those can occasionally be desirable.