How to count Status using Multi-select Drop-down list as input values

Nazeer Sayyed

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!

Danielle O'Connell

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.

Danielle O'Connell

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.

Nazeer Sayyed

Hi Danielle O'Connell

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

Nazeer Sayyed

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?

Danielle O'Connell

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.

https://app.smartsheet.com/b/form/739aa75f30ca43a8a22eb53e4da7d409?_ga=2.146965296.64631750.1612113948-351355438.1611592873

Nazeer Sayyed

Sure Danielle,

Thanks for your help! :)

Andrée Starå

Hi @Nazeer Sayyed

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!

Nazeer Sayyed

Hi Andree,

Yes the formula has more than 4000 chars.

Andrée Starå

@Nazeer Sayyed

Have you looked into using a VLOOKUP or INDEX/MATCH structure instead?

Nazeer Sayyed

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

Andrée Starå

@Nazeer Sayyed

Excellent!

Glad you got it working!