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

CJB

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!

Brett Wyrick

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!

Brett Wyrick

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!

Brett Wyrick

@CJB Does my solution work for you?

CJB

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

Brett Wyrick

https://community.smartsheet.com/discussion/comment/310620#Comment_310620

Glad to help!