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

Options
Nazeer Sayyed
Nazeer Sayyed ✭✭
edited 03/25/21 in Formulas and Functions

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

  • Danielle O'Connell
    Danielle O'Connell ✭✭✭✭✭
    Answer ✓
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!