Forum Discussion
Error when using SEQUENCE within COUNTIF
Excel won't accept this formula, and I don't know why: COUNTIF(SEQUENCE(20); "<9") Wrapping it with like the following is accepted by Excel, but produces 20 rows of VALUE errors: LET( ...
The short answer is COUNTIF (also, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, etc.) only accept ranges which have an address in the sheet (Sometimes referred to as 'target arrays'. Functions like SEQUENCE generate arrays "in-memory"). It's the same reason why an array constant is not accepted either:
=COUNTIF({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},">6"0
You could create a spill by using =SEQUENCE(20) and then refer to the spill using # notation and COUNTIF will accept it because the values have an address in the sheet.
You might also arrange the formula as such:
=COUNT(IF(SEQUENCE(20)>6,SEQUENCE(20)))
Excel's RACON functions | Exceljet