Compute Additional Hours Needed Using "if" or "lookup"

[Peter Snow]

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

Maybe someone could show me what I'm doing wrong please?

Many thanks.

 

[XLPadawan]

For one thing, the syntax for ANDing isn't the same for Excel as it is for SQL.

=AND(F4="Y",I4="Y") resolves to:
=AND("N"="Y","Y"="Y") resolves to:
=AND(FALSE,TRUE) resolves to:
FALSE​

This works in I3:

=SUM(IF(AND($F4="Y",I4="Y"),$E4,0),IF(AND($F5="Y",I5="Y"),$E5,0),IF(AND($F6="Y",I6="Y"),$E6,0))
​