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

Discussion in 'Excel' started by Peter Snow, Oct 17, 2015.

  1. Peter Snow

    Peter Snow

    Oct 17, 2015
    Likes Received:
    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!"

    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?

    screenshot knowledge spreadsheet.PNG

    Many thanks.
    Last edited: Oct 17, 2015
    Peter Snow, Oct 17, 2015
    1. Advertisements

  2. Peter Snow


    Jun 9, 2016
    Likes Received:
    Allen, TX
    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:
    This works in I3:
    XLPadawan, Jun 14, 2016
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.