Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.
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
=ARRAYFORMULA(IF((H2:H >= -B39)*(H2:H <= B40), 100, IF( H2:H > B37, 0, )))
                Thank you! Now I'm just wondering how was I supposed to know this. Is it documented somewhere? The error for sure is miss leading...
– Waltari
                Nov 22, 2021 at 11:00
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.