COUNT IFS of status with multiple drop down selections
Hello-
I need to calculate the total # of times a status appears, and it is also dependent on other criteria being met.
The current formula I am using will only be counted if that status is in the only status in the cell.
=COUNTIFS({Weekly Status Product Development Range 1}, "OFF TRACK", {Weekly Status Product Development Range 3}, "German")
I need to count the statuses in the screenshot below but the ones that also have "Tech" as part of the status are not being counted.
Best Answers
-
Hi @Liz Wallace,
Try using the HAS function and @cell for your multi-select dropdown column , so it'll look something like this:
=COUNTIFS({Weekly Status Product Development Range 1}, HAS(@cell, "OFF TRACK"), {Weekly Status Product Development Range 3}, "German")
Cheers,
Ric
-
Hi @Liz Wallace, great questions and keep 'em coming!
You're very close - to search for "2023" in this case, you'd want to use the CONTAINS function instead, so using your second formula, it'll look like:
=COUNTIFS({Weekly Status Product Development Range 4}, CONTAINS("2023", @cell), {Weekly Status Product Development Range 5}, "1")
HAS searches for specific values, so for that to work in your screenshot, you'll need to search for "2023-Fall". CONTAINS will search for strings or characters containing the criteria, hence it'll pickup "2023" in your Launch column.
Cheers,
Ric
Answers
-
Hi @Liz Wallace,
Try using the HAS function and @cell for your multi-select dropdown column , so it'll look something like this:
=COUNTIFS({Weekly Status Product Development Range 1}, HAS(@cell, "OFF TRACK"), {Weekly Status Product Development Range 3}, "German")
Cheers,
Ric
-
Much appreciated @Ric T - that worked!
-
@Ric T I have a similar question... I am trying to count the times the year appears in the black rows (Hierarch=0 for those rows)
I tried both =COUNTIFS({Weekly Status Product Development Range 4}, CONTAINS(@cell, "2023"), {Weekly Status Product Development Range 5}, "1")
and =COUNTIFS({Weekly Status Product Development Range 4}, HAS(@cell, "2023"), {Weekly Status Product Development Range 5}, "1")
But I am returning a zero for the value.
-
Hi @Liz Wallace, great questions and keep 'em coming!
You're very close - to search for "2023" in this case, you'd want to use the CONTAINS function instead, so using your second formula, it'll look like:
=COUNTIFS({Weekly Status Product Development Range 4}, CONTAINS("2023", @cell), {Weekly Status Product Development Range 5}, "1")
HAS searches for specific values, so for that to work in your screenshot, you'll need to search for "2023-Fall". CONTAINS will search for strings or characters containing the criteria, hence it'll pickup "2023" in your Launch column.
Cheers,
Ric
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!