Project by Country & Schedule Health


Hi there!

I am trying to populate a sheet (to create a widget for my dashboard). I have a project intake form which has the country and overall project health populated. I want to then create a table that counts all of the green, yellow, and red for each country (Japan, Germany, etc). I tried using Index/Match formulas but to no avail - I then tried COUNTIFS(INDEX(MATCH but it keeps erroring out. Can someone help?


Project Intake:


  • SmartLew
    SmartLew ✭✭✭✭

    just countifs will do it

    Your sheet is laid out a little strange with your Colours having column header "Country" which may make the below formula look a little confusing, but here it is without any changes needed to the sheet;

    =COUNTIFS({Schedule At Risk},$[Country]@row,{Country},[Austria]$1)

    You will be able to drag this down and across, as it is looking at the Country column (actually the colours) and the free text country names in row 1 as fixed positions.

    As you drag down it will look at green, then yellow, then red,

    As you drag across it will look at Austria, then Australia etc

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!