Utilizing the "COUNTIFS" and "HAS" for a dropdown list column

Options

I have been trying to use the "HAS" formula with a dropdown list column associated with another sheet using "COUNTIFS":

=COUNTIFS({Week Number}, =WEEKNUMBER(TODAY()), HAS({Actions Remaining}, =[Primary Column]@row))

I am currently receiving #INCORRECT ARGUMENT SET. I have a column listed in another sheet as "Actions Remaining" matching the "Primary Column@row" options. I have a column for the week # because there are multiple data points for each week and I only need the current weeks worth of data.

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Options

    Hi @dleathers,

    I'm not exactly clear what you need without seeing an example but try this.

    =COUNTIFS(({Week Number}, =WEEKNUMBER(TODAY()), {Actions Remaining}, HAS(@cell,=[Primary Column]@row))

    Hope this helps,

    Dave

  • dleathers
    Options

    The above formula did not work either.

    To give further context, The drop down list:

    This list is related to another sheet with the same "actions remaining" where users can select from the drop down list with multiple selections. I need a total count of the actions remaining from the other sheet for the current week.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    Hi @dleathers

    DKazatsky2's formula is fine, except for the = in the HAS function. In my demo sheets, the following works;

    =COUNTIFS({Week Number}, WEEKNUMBER(TODAY()), {Actions Remaining}, HAS(@cell, [Primary Column]@row))

    or

    =COUNTIFS({Week Number}, =WEEKNUMBER(TODAY()), {Actions Remaining}, HAS(@cell, [Primary Column]@row))

    But,

    =COUNTIFS({Week Number}, =WEEKNUMBER(TODAY()), {Actions Remaining}, HAS(@cell, =[Primary Column]@row))

    gives 0.

    Perhaps, as the help article below examples suggests, the HAS function expects simple text without an operator such as "=".🤔


    The Date and Actions Remaining columns are editable in the published Demo Dashboard below. So you can test how the formula works.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!