Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

A formula to count a series of "Health Status" in a column to display the status with the largest #?

✭✭✭

This one is tricky. I'd like to be able to count the # of green, yellow, red & blue statuses in a column (lower image) display on a dashboard (upper image). The other tricky part is that I only want to count the statuses on the rows that are Level 0. Any help would be greatly appreciated!




Best Answer

  • ✭✭✭✭✭
    Answer βœ“

    Couple options for this, but here's a straightforward answer.

    Go to your Sheet Summary.

    Add 4 new fields, add these formulas in them:

    Green Total

    =COUNTIFS([Overall Health]:[Overall Health], "Green")

    Yellow Total

    =COUNTIFS([Overall Health]:[Overall Health], "Yellow")

    Red Total

    =COUNTIFS([Overall Health]:[Overall Health], "Red")

    Blue Total

    =COUNTIFS([Overall Health]:[Overall Health], "Green")


    Then on your dashboard, add a new component, select "Metric". Click "Add Data", then on the "Select Data" screen, find your sheet, then select "Sheet Summary data:"

    Then select the Sheet Summary fields you want. For this example I include two:

    Then the dashboard component looks like this:

    You could also name your sheet summary fields as the corresponding emoji, so that way it's a little easier to read and takes up less space:

    πŸŸ’πŸŸ‘πŸ”΄πŸ”΅

    β€Ž

    Let me know if this works. This is how I'd probably do it!

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there πŸ‘€) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Answers

  • ✭✭✭✭✭
    Answer βœ“

    Couple options for this, but here's a straightforward answer.

    Go to your Sheet Summary.

    Add 4 new fields, add these formulas in them:

    Green Total

    =COUNTIFS([Overall Health]:[Overall Health], "Green")

    Yellow Total

    =COUNTIFS([Overall Health]:[Overall Health], "Yellow")

    Red Total

    =COUNTIFS([Overall Health]:[Overall Health], "Red")

    Blue Total

    =COUNTIFS([Overall Health]:[Overall Health], "Green")


    Then on your dashboard, add a new component, select "Metric". Click "Add Data", then on the "Select Data" screen, find your sheet, then select "Sheet Summary data:"

    Then select the Sheet Summary fields you want. For this example I include two:

    Then the dashboard component looks like this:

    You could also name your sheet summary fields as the corresponding emoji, so that way it's a little easier to read and takes up less space:

    πŸŸ’πŸŸ‘πŸ”΄πŸ”΅

    β€Ž

    Let me know if this works. This is how I'd probably do it!

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there πŸ‘€) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • ✭✭✭✭✭

    @CJB Does my solution work for you?

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there πŸ‘€) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • ✭✭✭

    Sorry for the delay again! Yes, I'll try this - I appreciate it!

  • ✭✭✭✭✭

    Glad to help!

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there πŸ‘€) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions