Explanation
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.
Explanation
Note: there are many ways to summarize data with COUNTIFS, SUMIFS, etc. This example shows one specific and arbitrary way. Before you go the formula route, consider a pivot table first , since pivot tables are far simpler to set up and do most of the hard work for you.
The table in B3:F11 is a log of course data where dates in columns D:F indicate course completion. The summary table in H3:K5 summarizes course completion by group instead of user. In this example, group represents the additional criteria.
For Course A, completion by group is calculated with COUNTIFS like this:
COUNTIFS($B$4:$B$11,$H4,D$4:D$11,"<>")
The first range/criteria pair counts only data from the red group by using the group value in H4. The second range/criteria pair counts non-blank entries in column D. The result is a count of all Course A completions for the Red group, 3 in this case.
To generate a total count of people in group Red, in order to calculate percent complete, another COUNTIFS is used:
COUNTIFS($B$4:$B$11,$H4)
The range/criteria pair counts total users in the red group by using the group value in H4, returning 3 in this case.
The result from the first COUNTIFS is divided by the result from the second COUNTIFS, and formatted with the Percentage number format to show percent complete.
