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

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭
    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

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭
    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!

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭

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

  • CJB
    CJB ✭

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

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭

    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!