Using Countif() to select fields to be included in computation

[snowweb]

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?

[Pecoflyer]

Hi
try
=SUMPRODUCT(($F$4:$F$38="y")*($I$4:$I$38="n")*$E$4 :$E$38)

as the conditions are not fulfilled the actual answer is 0. Juts play around with the y and n in col F and I

[snowweb]

Yup! Wow! That's perfecto!

Many thanks :-)

Peter