Complex Sumproduct Formula Not Translating to Google Sheets

[psycho_psanka]

I'm trying to convert a model that has a complex sumproduct formula:

"=SUMPRODUCT($AX14:CB14,N(OFFSET($BX$4:DB$4,0,COLUMN(DB$4)-COLUMN($BX$4:DB$4))))"

And once the column count gets to 31 (range of AX->CB...BX->DB), I get the following error:
"SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 31. Actual row count: 1, column count: 30."

When I copy the formula one cell to the left (range of AX -> CA...BX -> DA), the formula works fine:

SUMPRODUCT($AX14:CA14,N(OFFSET($BX$4:DA$4,0,COLUMN(DA$4)-COLUMN($BX$4:DA$4))))

I'd love any suggestions for a fix! Thanks in advance.

 

[ExcelElliott]

In order for the SUMPRODUCT function to work the array sizes must be the same.

The COLUMN(DA$4)-COLUMN($BX$4:DA$4) is always equal to 30, while $ax14:cb14 is a range of 31 columns, which is why going $ax14:ca14 works because that is only 30 columns.

I don't know the context of your data, but the best way to fix it is to ensure that the array sizes are equal.

Don't know if that's much help.