Forum Discussion
Nesting a COUNTIF With IF To Evaluate A Formula
Hello RangerLG​,
If the goal is to leave the result blank when no training is required, you don't need to combine COUNTIF with your existing formula. Wrap your calculation in an IF statement that checks whether the training is required.
For example, if Documents!B3 contains "Required":
=IF(Documents!B3<>"Required","",COUNTA(Documents!C3:C5)/3)
If the training is required, the formula returns the completion percentage. If it isn't required, it returns a blank.
If you don't already have a field that indicates whether training is required, how does your workbook identify employees who don't need that training? That will determine what condition to use in the IF statement.
Apologies for the late reply. What I am trying to achieve is a training matrix for when a training date is entered under an employee column into the sheet labeled "Documents", the matrix sheet will populate automatically with a percentage of completion for that particular job category. There are four colors I am using for the matrix depending on condition. Orange is 0% Trained, Cyan is In Progress, Green is Training Complete, and Gray means Training Not Required. For example, our job category "Receiving" has 10 job documents required to be trained on. Employee A is trained on 2 of those today, so I enter the date 7/28/2026 next to those two documents. After I do that, the matrix automatically populates that employee's cell with 20% and changes to cyan to indicate In Progress. I am currently achieving this with the formula COUNTA(Documents!xx:xx)/10. Not all job positions require training on all 75 documents, so what I want to do is instead of a date in the "Documents" sheet, I want to place an NR and have the matrix change the cell to a gray indicating that job does not require training in that category. My current sheet is quite convoluted and will take me some time to edit, but I will see what I can come up with.