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: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
