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

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

  1. Peter Snow

    Peter Snow

    Joined:
    Oct 17, 2015
    Messages:
    1
    Likes Received:
    0
    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?

    screenshot knowledge spreadsheet.PNG

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

  2. Peter Snow

    XLPadawan

    Joined:
    Jun 9, 2016
    Messages:
    32
    Likes Received:
    5
    Location:
    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:
    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))
     
    XLPadawan, Jun 14, 2016
    #2
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. chel
    Replies:
    0
    Views:
    484
  2. Nago
    Replies:
    0
    Views:
    517
  3. baldi
    Replies:
    1
    Views:
    767
    Sohail
    Dec 25, 2014
  4. phdman
    Replies:
    1
    Views:
    472
    Kevin@Radstock
    Nov 2, 2012
  5. caziscarol

    Excel help recording hours

    caziscarol, Oct 27, 2013, in forum: Excel
    Replies:
    1
    Views:
    509
    chimpytech
    Apr 12, 2014
  6. dragonus
    Replies:
    0
    Views:
    356
    dragonus
    Feb 10, 2014
  7. Peter Kaufmann

    Lookup Function Help

    Peter Kaufmann, Nov 8, 2014, in forum: Excel
    Replies:
    1
    Views:
    344
    Peter Kaufmann
    Nov 13, 2014
  8. dubgurl
    Replies:
    1
    Views:
    257
    XLPadawan
    Jun 9, 2016
Loading...