Forum Discussion

YeBoldeSquirrel's avatar
YeBoldeSquirrel
Copper Contributor
Feb 18, 2023

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(
sequ; SEQUENCE(20);
COUNTIF(sequ; "<9")
)

 

All I want is the count of numbers in the sequence that are <9. Any ideas?

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    YeBoldeSquirrel 

    COUNTIF() needs a reference and not an array.

     

    This works:

    Formula in A1

    =SEQUENCE(20)

    Formula in B1

    =COUNTIF(A1#,"<9")

     

    Or you could use mathematical logic:

    =9-1

     

    • YeBoldeSquirrel's avatar
      YeBoldeSquirrel
      Copper Contributor

      Detlef_Lewin Patrick2788 

       

      Thanks for the explanations, that's interesting to know about this limitation. 

       

      =COUNT(IF(SEQUENCE(20)>6,SEQUENCE(20)))

      is a good workaround. 

       

      I found out that this one works just as well:

      =SUMPRODUCT(IF(SEQUENCE(20)>6, 1, 0))

       

      I'll have to think a bit longer about this one:

      =9-1

       

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    YeBoldeSquirrel 

    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

     

     

     

Resources