Using Countif() to select fields to be included in computation
I am making a spreadsheet to track learning of multiple documents by multiple employees. It has the following columns:
1) list of documents
2) number of estimated hours to study each document
3) y/n whether this document is "required" learning or not.
4) y/n whether studying it is completed
5) One column for each employee who has to accomplish the learning.
Under each employee's name (for example column "i3") I have a field for "Additional Hours Needed". I want this to display the total time needed to learn all documents which are as yet unlearnt but are marked as required but I can't get the syntax right for the formula.
Currently, the formula I have is:
=IF(COUNTIF(F4:F38,"y") AND COUNTIF(I4:I38,"<>y"),E4:E38,0)
The result of that is "#VALUE!"
Where:
F4:F38 is the range of y/n whether the learning is "required"
I4:I38 is the range where the y/n is recorded concerning whether the user has completed the item
E$:E38 is the range where the number of hours estimated to learn each document from scratch is stated.
The result (number of hours remaining) should be stored in I3
I have attached a cut down version of the spreadsheet.
Maybe someone could show me what I'm doing wrong please?
|