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.
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.