# Course completion summary with criteria

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:

### 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.

## Excel Formula Training

Learn Excel formulas and functions with concise, clear videos. Master absolute/relative addresses, dates, text, named ranges, and tools for troubleshooting. Each video comes with a practice worksheet and audio transcript. Instant access and complete 100% guarantee. Start today!