COUNTIF looking at two columns and 13 facts

[johnandbeth]

I want to automate something I do manually that takes a long time. I have a spreadsheet with training course data. I need to countif (I think) and show learner name (column B) who have "completed" (column F) a list of 13 courses (column G). I have the names of the 13 courses as they show in the spreadsheet. Right now, I do a subtotal and then if the learner name shows that they are in the spreadsheet 13 times or more, I check to see if they have all of the courses in the program showing as completed. It is very manual and subject to human error. Is there a way to do this with a function in Excel?
Thanks!

 

[Asala42]

This might be easy or hard depending on the data you're working with.
Is the data just an export of all completed courses (1 record per user and course?).
Does this data include extra records? Such as perhaps incomplete courses or completed courses for those other than the 13 of interest?

I'd probably use a pivot table here, which would give you a quick count of courses per user. Select range and do an Insert>Pivot Table.

A helper formula column might also be useful in an adjacent field to your base export. For example you could list the 13 courses somewhere and use a countif "=COUNTIF(My13Courses,G2)", which would flag a 0 or 1 if the course name shows up in your manual list. A similar helper column could also be used if you're only interested in viewing results for a manual list of specific learner names.

HTH

 

[johnandbeth]

Thank you for the reply HTH. My concern is yes, there are other course status on the spreadsheet (registered, in-progress). So we would want the tracking to filter out these to just status of completed and look for a specific group of courses, not just any 13. There are 32 courses in the program and I want to look for specific courses that show a person completed a specific level or levels. I need the function to actually look for four different groups of courses and provide the names of the people who show completed for the entire group of courses. I hope this makes sense.
Thanks!