Feature request: multi-reference ranges (eg. A1:B2:C3:D4:E5)

[argandsolutions-glyn]

Idea
Could we add support for individual cells within the sum/criteria ranges for SUMIFS?

Example
=SUMIFS(A2:B2:C2, D2:E2:F2, ">1")

At present, i've found that only ranges (sum or criteria) within the same column are supported i.e. =SUMIFS(A2:A4, B2:B4, ">0")

I'd be happy to contribute towards this functionality if that would be helpful.

[sequba]

Hi @argandsolutions, thank you for reaching out.

I'm not sure what you mean. The example you provided works in HyperFormula if you remove the redundant middle cell addresses from the range specifications:

import HyperFormula from 'hyperformula';

const hf = HyperFormula.buildFromArray([
  ['=SUMIFS(A2:C2, D2:F2, ">1")'],
  [1, 2, 3, 4, 5, 6]
],{
  licenseKey: 'gpl-v3',
});

console.log(hf.getSheetValues(0));

Can you describe the requested feature in more detail and provide more examples?

[argandsolutions-glyn]

Yes certainly.

What i'm looking to achieve is the following:

import HyperFormula from 'hyperformula';

const hf = HyperFormula.buildFromArray([
  ['=SUMIFS(A2:B2:C2, D2:E2:F2, ">1")'],
  [1, 2, 3, 4, 5, 6]
],{
  licenseKey: 'gpl-v3',
});

console.log(hf.getSheetValues(0));

// The above results in "Parsing error. Expecting token of type --> RParen <-- but found --> ':' <--"

This appears to be possible in Excel:

At present, in my script I am building up the formula and dataset programatically and it would take further processing in order to reduce the formula inputs to a range (A2:C2) rather than individual cell references (A2:B2:C2).

I hope that makes sense. If not, I can provide additional context and examples.

[sequba]

ODFF reference on ranges: https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1018032_715980110

[argandsolutions-glyn]

Ah ok. That's understandable.

I'd be happy to close this discussion if the feature i'm interested in isn't part of the OpenFormula format?

[sequba]

@argandsolutions It's a valid feature request. It just needs the proper specification. The HyperFormula team will probably consider it low-priority, but it's still worth discussing.

The main issue is that, as far as I can see, Microsoft Excel interprets such ranges differently from the OpenFormula standard.

OpenFormula

According to the Standard, range A1:B2:C3:D4:E5 is equivalent to A1:E5.

Microsoft Excel

I couldn't find any official reference describing the syntax of range specification in Excel, but it seems that A1:B2:C3:D4:E5 is interpreted as the sum of 3 ranges: A1:B2 + C3:D4 + E5:E5:

If we were to consider adding this feature, we would need to choose one of the above semantics or implement both and introduce a configuration option to switch between them.

[argandsolutions-glyn]

Thanks for your thoughts @sequba

As you say, it's difficult as the specification differs between the Excel implementation of the ranges used in the SUMIFS function and the OpenFormula standard. It then raises the question of whether HyperFormula is based on the OpenFormula standard or a hybrid of the two (Excel and OpenFormula), which might then cause confusion for users. As you mentioned before, you'd need a configuration option to allow users to switch between the two.

I wouldn't know how to proceed with this. It's essentially behaving fine if your expectations are based on OpenFormula but not if it's based on Excel.

Perhaps there's nothing we can do right now?

[sequba]

We stick to OpenFormula as much as possible while keeping in mind that most users expect the same behavior as in Excel. In this case, the ideal option is to make it configurable.

The Microsoft Excel interpretation is much more challenging to implement. It may require significant changes in the core of the engine. Also, it raises a question about how other functions should operate on such non-rectangular ranges (e.g. TRANSPOSE).

As I wrote above, either way, this feature is low-priority at the current stage of development, so we'll keep it as an idea for the future.

[argandsolutions-glyn]

That's understandable. It makes sense that it would be challenging to change how the core operates to accommodate Excel's behaviour.

Shall I close this discussion? Or does it need labelling to mark it as a future idea?

[sequba]

Let's keep it open in case someone else is also interested in this feature ;)

[argandsolutions-glyn]

Cool :)