How to count Status using Multi-select Drop-down list as input values
Hi There,
I'm new to Smartsheet and I need your help with formulas/functions.
I have a mapping sheet with below two columns. Currently I have 30+ scenarios and this list may increase in future.
In my master sheet, I have multi-select dropdown column [Scenario Name]. I need to reference mapping sheet and show [Status: Delivered] and [Status: WIP] count for each cell based on the scenario's selected from the drop-down list.
Please can someone help me resolve above challenge.
Appreciate your help!
Best Answer
-
You could use something like this for WIP.
For each scenario you name you would have to do the first part of the formula:
IF(CONTAINS("Object not Scenario Dependent", [Scenario Name]@row), COUNTIFS({Scenario Name}, "Object not Scenario Dependent", {Status}, "WIP"), 0)
Then add the same formula updating the scenario
=IF(CONTAINS("Object not Scenario Dependent", [Scenario Name]@row), COUNTIFS({Scenario Name}, "Object not Scenario Dependent", {Status}, "WIP"), 0) + =IF(CONTAINS("Standard Functionality Complete", [Scenario Name]@row), COUNTIFS({Scenario Name}, "Standard Functionality Complete", {Status}, "WIP"), 0) + ...
Keep doing this until you have all of your scenarios. Then repeat, replacing WIP with Delivered for that column.
You may know this, buts since you said you are new to Smartsheet I wanted to also note that the { } brackets represent referencing another sheet - so this would be your mapping sheet. Just click on the blue link to do that.
[ ] brackets are for columns in your sheet with the formula.
Answers
-
You could use something like this for WIP.
For each scenario you name you would have to do the first part of the formula:
IF(CONTAINS("Object not Scenario Dependent", [Scenario Name]@row), COUNTIFS({Scenario Name}, "Object not Scenario Dependent", {Status}, "WIP"), 0)
Then add the same formula updating the scenario
=IF(CONTAINS("Object not Scenario Dependent", [Scenario Name]@row), COUNTIFS({Scenario Name}, "Object not Scenario Dependent", {Status}, "WIP"), 0) + =IF(CONTAINS("Standard Functionality Complete", [Scenario Name]@row), COUNTIFS({Scenario Name}, "Standard Functionality Complete", {Status}, "WIP"), 0) + ...
Keep doing this until you have all of your scenarios. Then repeat, replacing WIP with Delivered for that column.
You may know this, buts since you said you are new to Smartsheet I wanted to also note that the { } brackets represent referencing another sheet - so this would be your mapping sheet. Just click on the blue link to do that.
[ ] brackets are for columns in your sheet with the formula.
-
Appreciate your quick reply and solution.
Sorry for the confusion, In the question above, I have given only 6 scenario but I have more than 30 scenarios which may increase in future. That's the reason I'm using Scenario Mapping sheet. Is there a way to read individual item from the multi-select cell dynamically and count Status from the Mapping Sheet? Thanks, Nazeer
-
Thank you Danielle for your help. I was able to get the count using your suggestion. But it seems there is some limitation for no. of conditions/chars and I'm not able to add all Scenarios in the IF/CONTAINS/COUNTIF formula.
Do you have any suggestion here?
-
I don't unfortunately. I would suggest putting in a product enhancement request. The developers are really good about taking user feedback when they make updates.
-
Sure Danielle,
Thanks for your help! :)
-
I hope you're well and safe!
Is your formula more than 4000 characters (the limit)?
I hope that helps!
Be safe and have a fantastic day!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andree,
Yes the formula has more than 4000 chars.
-
Have you looked into using a VLOOKUP or INDEX/MATCH structure instead?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Danielle O'Connell and for others who need help with same, I was able to achieve it using JOIN, COLLECT and CONTAINS functions.
=JOIN(COLLECT({mapping sheet Range 2}, {mapping sheet Range 1}, CONTAINS(@cell, [Scenario Name]@row)), ", ")
The above calculation gives me result as show below.
Then, I calculated the no. of occurrence of 'Delivered' from Status (Delivered) column. Thank you @KDM for below solution.
https://community.smartsheet.com/discussion/77711/count-string-occurrence-in-a-cell/p1?new=1
Thanks for all your help.
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!