Multiple criteria search and return a single value..continued

[FreeOT]

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!

[NBVC]

I'm afraid I don't quite understand the new criteria or under what logic to include those courses you mention.

Can you elaborate?

[FreeOT]

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

[NBVC]

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")

[FreeOT]

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?

[NBVC]

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",""))

[FreeOT]

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!