Exceljet

Quick, clean, and to the point

Course completion summary with criteria

Excel formula: Course completion summary with criteria
Generic formula 
=COUNTIFS(rng1,group,rng2,"<>")/COUNTIFS(rng1,group)
Explanation 

This example illustrates one way to summarize course completion data using a criteria, in this case "group". In the example shown, the formula in I4 is:

=COUNTIFS($B$4:$B$11,$H4,D$4:D$11,"<>")/COUNTIFS($B$4:$B$11,$H4)

How this formula works

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.

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 really appreciate your delivery of the videos, the clean and sharp documentation and pleasant to the eye website, uncluttered, direct, short and snappy. From what I've checked out elsewhere nothing compares... - Christina
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