Cell range not allowed

[coder-xiaotian]

Description

I used SUMPRODUCT function in my sheet. Then exec the code below

hfInstance.getCellValue({
    sheet: 0,
    row: 2,
    col: 11
  })

I get the error below

DetailedCellError {
  value: '#VALUE!',
  address: 'Sheet1!L3',
  type: 'VALUE',
  message: 'Cell range not allowed.'
}

what is the meaning of it?

Steps to reproduce

Here is my sheet data.

{"Sheet1":[["颜色","XS","S","M","L","XL","2XL","颜色","尺码","SKU","ASIN","入库总数"],[null,null,null,null,null,null,null,null,null,"总",null,"=SUM(L3:L200)"],["白底红花",null,2,4,6,3,5,"48#黑色","2XL","JA033-01Black-2XL-FBA","B09F345ZCS","=SUMPRODUCT(($A$3:$A$257=$H3)*($B$1:$G$1=$I3)*$B$3:$G$257)"],["K2-009 4#红橙点",null,null,null,7,2,7],["45#酒红",null,null,null,1,12,null],["1#白色",null,3,12,8,13,4],["K2-009 4#红橙点",null,2,4,null,null,null],["白色印花",null,4,8,5,10,3],["45#酒红",null,6,6,null,null,null],["黑色波点",null,4,4,1,4,5],["48#黑色",null,3,3,2,1,5]]}

• HyperFormula version: 1.3.1
• Browser Name and version: Chrome 98.0.4758.80
• Operating System: macOS 12.1

[sequba]

@775146061 The error is caused by this formula:

=SUMPRODUCT(($A$3:$A$257=$H3)*($B$1:$G$1=$I3)*$B$3:$G$257)

We do not support the *-syntax for SUMPRODUCT function, but if I understand your intentions correctly, this can be achieved in HF by the following formula:

=SUM(ARRAYFORMULA(($A$3:$A$257=$H3)*($B$1:$G$1=$I3)*$B$3:$G$257))

[izulin]

Setting the appriopriate config flag would also work here:

https://github.com/handsontable/hyperformula/blob/master/docs/guide/arrays.md#enabling-the-array-arithmetic-mode-globally