Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulanested-ififs

IFS has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 1000, column count: 1

My formula is as follows:

=ARRAYFORMULA(IFS(AND(H2:H >= -B39, H2:H <= B40), 100, H2:H > B37, 0))

However this line keeps throwing the error:

IFS has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 1000, column count: 1.

Any idea how to fix this?

I have similar formulaes like this one that works:

=ARRAYFORMULA(IFS(F2:F <= 0, 100, F2:F >= B19, 0, true, (B19 - F2:F) / B19 * 100))

Not sure what I'm doing so differently with the first formula that would cause the issue.

Solution

  • AND is not supported under ARRAYFORMULA

    =ARRAYFORMULA(IFS((H2:H >= -B39)*(H2:H <= B40), 100, H2:H > B37, 0))

    and IFS is in some cases not suited for ARRAYFORMULA as well

    try:

    =ARRAYFORMULA(IF((H2:H >= -B39)*(H2:H <= B40), 100, IF( H2:H > B37, 0, )))