Project by Country & Schedule Health

sj2022

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?

Table:

Project Intake:

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