Number of States
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

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 200 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!