Exceljet

Quick, clean, and to the point

Course completion status summary

Excel formula: Course completion status summary
Generic formula 
=IF(COUNTIFS(rng1,crit1,rng2,crit2),"x","")
Explanation 

To build a summary to show course completion status based on a data log, you can use the COUNTIFS function together with the IF function.

In the example shown, the formula in G4 is:

=IF(COUNTIFS(name,$F4,course,G$3),"x","")

How this formula works

The table in B3:D11 is a log that shows courses completed by various people. If a course has been completed by a person, there will be an entry in the table with name, course, and date. For the purpose of this example, if we find and entry for a given name/course, we can assume that course is complete.

In the summary table in F3 to I7, we have the 4 names that appear in the data log in rows, and 3 courses we want to track as column headers. Note names and courses match entries in the data log exactly.

The core of the formula is the COUNTIFS function, which is configured with 2 range/criteria pairs. The first pair matches on the named range "name" (K5:K11) with criteria coming from $F4 (with column locked to allow the formula to be copied across the table). The second pair matches on the named range "course" (L5:L11) with criteria coming from G$3 (with row locked to allow the formula to be copied down the table).

The COUNTIFS function counts instances of each name and course in the log, using values in the summary table. When a name and course is found, COUNTIFS returns the number 1. When a name and course is not found, COUNTIFS returns zero.

We catch these results with the IF function, where COUNTIFS appears as the logical test. IF will evaluate any positive number as TRUE, and any zero result as FALSE, so we simply provide "x" for value if TRUE and an empty string ("") for value if false.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting work done in Excel. In this step-by-step training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Start building valuable skills with Excel formulas today. Learn more.

I have relied on your website on countless occasions throughout the course of my graduate level finance internship. Compared to a number of unnamed Excel tutorial sites and forums, yours is par-none. It is invariably the only source for consistently accurate and understandable formula functions and syntaxes. I say that as this page has solved an hours-long problem facing me during an all-night work session. Thanks a lot! - Nick
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course