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?

Attached Files

Knowledge Transfer Tracker (for assistance).xlsx (14.5 KB, 9 views)

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

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Yup! Wow! That's perfecto!

Many thanks :-)

Peter