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.
Answers
-
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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!