Results 1 to 7 of 7

Thread: Multiple criteria search and return a single value..continued

  1. #1

    Multiple criteria search and return a single value..continued



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello. Iím working on a report that shows when employees have completed all of their online courses. So far, I have two sheets; sheet1 is a report based on raw data from sheet2.
    On sheet2, column 1 is employee name, column 3 is name of the course, column 6 is completion status.
    Member NBVC gave an excellent solution earlier, but Iíve been informed there are more criteria that need to be included.
    I need a formula that can cross reference the employee name on sheet1 with the raw data on sheet2 to determine if the employee has completed all the training. The problem is the courses on sheet2 have multiple names for the same course. For example; Check-in, xxCheck-In, Check-In (7-14).
    Please see attachment for clarification.
    On sheet1, cell E3 would have a formula that would result in the text "Done", as this employee has completed the following courses from Column C sheet2:
    Build Loyalty
    Owning Activation: St. Regis Butler Service
    Owning Activation: Rituals- Afternoon Tea
    Owning Activation: Rituals- The Bloody Mary
    Connect
    Delight
    Practical Recovery
    Creating The Experience: St. Regis Butler Service
    Creating The Experience: Rituals- Afternoon Tea
    Creating The Experience: Rituals- The Bloody Mary
    Build Relationships
    Be the Brand


    See attachment for clarification.
    Note: Course names can alter slightly with date or other text being added prior to after course name.

    You guys/gals are awesome!
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    I'm afraid I don't quite understand the new criteria or under what logic to include those courses you mention.

    Can you elaborate?


  3. #3
    Hello NBVC,
    Sorry for the confusion. I highlighted the courses on sheet2, which is named Data_dump.
    On sheet "Data_dump" you'll see all of the courses Alfred Adams has taken in column C. His name is in Column A, and the completion status is in column F. There are 12 core courses that I'm concerned with for this formula, the rest are irrelevant. I've highlighted the 12 core courses in yellow. So, the formula would return "Done" in text to sheet1 "By_employee" once he has completed all core courses. But only after he has completed all 12 core courses, which would be indicated by "Completed" for each course in column F Data_dump.
    Does that make sense?

    Also, in the formula you provided a few weeks ago, there are actually two possible results, "completed" and "in progress". Is there a way to modify the first formula you sent to show either, depending on the status? This formula is for only one class, so relevant info would all be on the same row.

    =IF(COUNTIFS(Sheet2!$A:$A,$C5,Sheet2!$C:$C,"*"&F$2&"*",Sheet2!$F:$F,"Completed")>0,"Done","")

    Thank you.

    D
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Ok.

    First put those 12 courses in a table on the side of it's own. Say in Sheet1, U4:U15

    Then in E5 use formula:

    =IF(C5="","",IF(SUMPRODUCT(COUNTIFS(Data_dump!$A$1:$A$285,C5,Data_dump!$C$1:$C$285,By_employee!$U$4:$U$15,Data_dump!$F$1:$F$285,"Completed"))=COUNTA($U$4:$U$15),"Done",""))

    copied down. (you can cut/paste the table elsewhere if desired, and the formula will stay in tact).


    in the second part, do you mean, in F5:

    =IF(COUNTIFS(Data_dump!$A:$A,$C5,Data_dump!$C:$C,"*"&F$2&"*",Data_dump!$F:$F,"Completed")>0,"Done","In Progress")
    Attached Files Attached Files


  5. #5
    Thank you NBVC, the first formula works like a charm. So well so, that we've discovered that a certain individual, my boss, has not been updating the record like he should, haha!

    The second formula I should clarify something.
    =IF(COUNTIFS(Data_dump!$A:$A,$C5,Data_dump!$C:$C,"*"&F$2&"*",Data_dump!$F:$F,"Completed")>0,"Done","In Progress")
    There are actually 6 possible outcomes, but my concern is with "completed" and "in progress". If the employee hasn't completed or is not in progress, then the cell should remain blank. Is that possible?

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =IF(COUNTIFS(Data_dump!$A:$A,$C5,Data_dump!$C:$C,"*"&F$2&"*",Data_dump!$F:$F,"Completed")>0,"Done",IF(COUNTIFS(Data_dump!$A:$A,$C5,Data_dump!$C:$C,"*"&F$2&"*",Data_dump!$F:$F,"In Progress")>0,"In Progress",""))


  7. #7
    Quote Originally Posted by NBVC View Post
    Try:

    =IF(COUNTIFS(Data_dump!$A:$A,$C5,Data_dump!$C:$C,"*"&F$2&"*",Data_dump!$F:$F,"Completed")>0,"Done",IF(COUNTIFS(Data_dump!$A:$A,$C5,Data_dump!$C:$C,"*"&F$2&"*",Data_dump!$F:$F,"In Progress")>0,"In Progress",""))

    Excellent! Works like clockwork, and I really appreciate your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •