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.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!