Best Of

There's a couple different ways to do this.

Use the sheet summary. Open the Sheet Summary and create a field, then build out your formula (something similar to below).

Then on the dashboard, select the Metric widget, find the sheet, then select "Sheet Summary data" as the source, and choose your option.

To create a graph, build a grouped report and summarize the Health column

Now go back to your dashboard and create a Chart widget with this report as your source. You'll have to adjust the settings on the right side to get exactly the look you want, but in the end it can be something like this:

For complicated builds you can build what I call my "Dashboard Metrics" sheets where you can do a lot more difficult formulas for fancier graphs, but as a beginner, this should make you dangerous enough!

Try using the @cell for your range instead

=COUNTIFS([ROI Type]:[ROI Type], HAS(@cell, "organizational competitiveness"))

Give this a go:

=IF(FIND("/", [Column Name]@row) <> 0, VALUE(LEFT([Column Name]@row, FIND(" ", [Column Name]@row) - 1)) + (VALUE(MID([Column Name]@row, FIND(" ", [Column Name]@row) + 1, FIND("/", [Column Name]@row) - (FIND(" ", [Column Name]@row) + 1))) / VALUE(RIGHT([Column Name]@row, LEN([Column Name]@row) - FIND("/", [Column Name]@row)))), [Column Name]@row)