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( ...
excel
Formulas and Functions
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Feb 18, 2023

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
    Feb 18, 2023

    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

     

Resources