Forum Discussion
Nesting a COUNTIF With IF To Evaluate A Formula
I have a sheet called Employee Training Matrix that I use to lookup data in a tab called Documents to see if there has been a date entered into a range of cells. Depending on how many dates are entered, the sheet will calculate the percentage where a person has been trained for a particular job. For example, the safety training requires three documents, so the formula I use for this lookup is "=COUNTA(Documents!C3:C5)/3". The issue is that some people do not require training in some areas, so I want to have the sheet to return a blank cell that I will format with a conditional format rule. Is there a way to do an IF and COUNTIF formula that will return a blank cell on one sheet when the second sheet has no date and run my COUNTA formula if there is? Or is there another way to address this? Thank you.
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.
4 Replies
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.
I agree with mathetes that we could use more information and details. From what I read I am guessing 1 of 2 things (but both may be wrong):
a) if you have more than 1 criteria to consider (e.g. if there is a date there and if they are required to do that training) then you want to use COUNTIFS so something like:
The following will check for any value in cells C3:C5 and that there is ALSO a value of "Required" in column D:
=COUNTIFS(Documents!C3:C5,"<>",Documents!D3:D5,"Required")
b) if all you want is to have it blank instead of showing 0 (zero) for the count then either use Number Formatting to make zero show as blank (see below) or you can add an IF or and IFERROR like so:
In this case I use LET to set the result to a temporary variable called "a" and then check if "a" is anything but zero then show the value from "a" otherwise show blank (""):
=LET(a, COUNTA(Documents!C3:C5)/3, IF(a, a, ""))
In this case I use a trick of anything over zero causing an error so 3/COUNTA(...) will cause a Divide by Zero error and hence the IFERROR will show blank (""), otherwise it will do the 1/ operation to reinvert the fraction to the value you want:
=IFERROR(1/(3/COUNTA(Documents!C3:C5)), "")
If you want to use Number Formatting to make zeros show as blank you need to go to number formatting - More Number Formats => then select "Custom" from the left pane and then in the box under "Type:" enter something like:
#;-#;;@
this will show positive numbers, negative numbers with a - sign, zeros are blank and text is text. This should work for you but if you needed decimal points or other formatting you would need to tweak the formatting.
I suspect that what you're trying to do is fairly easy. Unfortunately, I don't really know what it is you're trying to do and your explanation is, frankly, not clear enough to really get a grasp on what that is. Is it possible for you to attach a copy of your workbook--removing any real identities, replacing them with Disney character names, as well as concealing any other proprietary data (e.g., if there is possibility of inferring too much from the names of the safety training modules. You should be able to attach such a sample sheet to your reply. If not, then post it in Google Drive or Drop Box and post a link here.
