=(COUNTIF(DATA_Training_Matrix!E2:E300,"<"&TODAY())/(((COUNTIF(DATA_Training_Matrix!E2:E300,"-")+COUNTIF(DATA_Training_Matrix!E2:E300,"<"&TODAY())))))
It works, but it’s a bit clunky – it returns a percentage that indicates the amount of the dates in the column are previous to today – as opposed to having a dash in that column. It was the only way I could think of to make it work. I want to know a percentage of people that have completed a training course – and the data set records a date for completion, or a dash for not having completed it.
My logic was to COUNTIF all dates less than &TODAY() add those to the fields that only contain a dash, and then divide by the COUNTIF dates less than &TODAY() to return a percentage.
Grateful for any tips.
Josh Durham is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1