Number of States

Options

I have two columns: Call Type (4 dropdown options), and State (54 dropdown options). I was able to calculate how many states have had a specific type of call using this formula: =COUNT(DISTINCT(COLLECT(State:State, [Call Type]:[Call Type], "Project Call")))

How do I show the number of states that have had a project call (n=18) over the total number of states (n=54)? Ideally I want to create a single stacked bar (N= 54) that shows the states with project calls (n=18). The goal is to be able to see progress towards all states having had a project call.

Thank you!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Mallory Rousseau

    If I'm understanding what you're looking to do, you've already done most of the hard work! At this point you just need to find out how many states are left over... or, take 54 and subtract the value from your formula.

    Set up a second column next to your formula column, like so:

    Then you can use these values in a stacked bar chart to total 54:


    The legend on the side of the graph will be based on your column names. See: Compare Live Data with a Chart for more information.

    Let me know if you need help setting this up!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Mallory Rousseau

    If I'm understanding what you're looking to do, you've already done most of the hard work! At this point you just need to find out how many states are left over... or, take 54 and subtract the value from your formula.

    Set up a second column next to your formula column, like so:

    Then you can use these values in a stacked bar chart to total 54:


    The legend on the side of the graph will be based on your column names. See: Compare Live Data with a Chart for more information.

    Let me know if you need help setting this up!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!