Countif based on array of values
Hello,
I hope this is possible!🤣
I have this list of states where a "Task Name" can have multiple sub-states listed on "Column 1"
I've already "merged" the sub-states. Example below with Blocker --> "Test Blocked" & "Blocked"
=JOIN(COLLECT([Column 1]82:[Column 1]89, [Task Name]82:[Task Name]89, [Column 4]86), " ")
and now what I would like to reach is a countif on my database of values, that would be count the number of Stories that either have "Test Blocked" or "Blocked" as I'm selecting the parent "Blocked"
I case I would have "To Do" I would search for all sub-states under To Do, that would be "To Do", "new", "Pending Sign off" or "Reopened"
So far I've been working with Or(contains()), where I have a contains for each type, but every time a sub-state changes I need to re-arrange all my formulas manually, so I'm seeking for a more automated way.
Thank you in advance!
Answers
-
Pretty sure you're going to want a nested if function. Try this to get a master status:
=if(or([column1]@row = "blocked", [column1]@row = "test blocked"), "BLOCKED", if(or([column1]@row = "to do", [column1]@row = "new", [column1]@row = "pending sign off", [column1]@row = "reopened"), "TO DO", if([column1]@row = "ready, "READY", if([column1]@row = "research", "RESEARCH", "ERROR"))))
If you're truly just looking for a count, try:
=countif([column1]:[column1], or([column1]@row = "to do", [column1]@row = "new", [column1]@row = "pending sign off", [column1]@row = "reopened")
-
hello @Austin Smith thank you for your prompt reply.
I do not want to select the sub-stages manually, I wish to depending on the master stage, be able to use the sub-stages on a CountIf formula.
Example:
If I want to search for "Blocked" I effectively want for the CountIf to have as criteria the Sub-Stages ("Test Blocked and "Blocked") without me having the need to list those.
Maybe it's not possible to count an array of options without having the possibility to write them manually.
Thank you!
-
Try the following formula in the highlighted cell under To Do. (where you show 2)
=countif([column5]:[column5], OR(@cell = "to do", @cell ="new", @cell ="pending sign off", @cell ="reopened"))
You'll just need to change the trigger words for Blocked sub-categories for the Blocked count cell. You don't need a written master list of available options for it to work.
=countif([column5]:[column5], OR(@cell = "blocked", @cell ="test blocked")
-
Hello @Austin Smith Thank you for your support.
I have added your formula and then I have changed a sub-stage from "New" to "New Status" and instead of the To Do give me 2 counts only gives me 1.
I would like to find a way to sum the number of times a sub-stage appears based on the defined stage, without having to write the sub-stages in the formula.
In Excel I have reach this:
The numbers is purple 1 and 2 are corresponding to the number of times the sub-stages of Blocked appear. 1 for Test Blocked when looking for Blocked + Story on the column E and F and 2 for the Blocked when looking for the Blocked + Story on column E and F, of course based on the master and sub-stages on column B and C.
The final step would be to be able to sum both in the same formula! ahahah and then figure out how to do that in Smartsheet!
Thank you so much for the support and prompt replies!
-
Something like this would be enough and so I can work on a workaround.
-
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hello @Genevieve P. ,
Apologies for my absence ( I took a time-off in the last days)
Yes, I was able to have a workaround for both situations after @Paul Newcome suggestion in the other post!
Thank you, for all the support 🤗
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!