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

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.

Find more posts tagged with

Accepted answers

All comments

DKazatsky2

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

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

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))

=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 "=".🤔

https://help.smartsheet.com/function/has

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

https://app.smartsheet.com/b/publish?EQBCT=8ef3f10d3e7f476fb981010a71de121a

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.