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.
Make the requirement list drive the formula instead of trying to infer it only from the training-date cells. For each employee/job combination, first count how many required documents exist in the Position Requirements table. If that count is zero, return a blank string; otherwise, divide the completed training dates by the required-document count. In Microsoft 365, this is usually easier with structured tables and COUNTIFS. For example, one COUNTIFS for required documents by job and training area, and another COUNTIFS for completed dates for that employee. The important part is that blank should mean 'not required', not 'required but incomplete', so keep those two states separate in the formula.