In this tutorial, you will learn how to summarize course completion data with criteria. Criteria in our example will be course level marked in column “Level”. Range1 will be the column with level data, and range2 will be the column with course completion date. We want to calculate the percentage of each course completion under defined criteria. You can do this by using COUNTIFS formula with the syntax defined in cell I3:
=COUNTIFS(range1,criteria1,range2,"<>")/COUNTIFS(range1,criteria1)
 Course completion summary with criteria
 Course completion summary with named ranged criteria

Course completion summary with criteria
As we have already mentioned, we want to summarize course completion data with specific criteria, course level. The result will be in the separate table with columns course level and course type, showing the percentage of completion of each course type and course level. Formula syntax for solving this problem is:
=COUNTIFS(range1,criteria1,range2,"<>")/COUNTIFS(range1,criteria1)
=COUNTIFS($B$3:$B$12,$H3,D$3:D$12,"<>")/COUNTIFS($B$3:$B$12,$H3)
Course completion summary with criteria
Let’s go through formula syntax step by step. First, we want to calculate the number of completed Course 1 on the Basic level. Range1 is column Level, criteria 1 is level Basic in the first part of the COUNTIFS function. Range 2 is defined by data in column Course 1, and criteria 2 is ignoring blank cells, “<>”.
=COUNTIFS(range1,criteria1,range2,"<>")
=COUNTIFS($B$3:$B$12,$H3,D$3:D$12,"<>")
Please note that all cells and ranges in formula use either absolute reference or combination of absolute and relative references. This is very important if you want to copy a formula to other cells. To create absolute reference you just have to put symbol $ in front of the row or column. If you drag formula with symbol $ in front of the row and column, they will remain the same in the next cell.
The second part of the formula syntax is the calculation of the total number of level Basic courses in column “Level”. Range 1 is Column “Level”, and criteria1 is the Basic course. Since there is just one condition we can also use COUNTIF function.
= COUNTIFS(range1,criteria1)
= COUNTIFS($B$3:$B$12,$H3)
When we divide two COUNTIFS formulas, the result will be the percentage of each course completion with criteria course level.

Course completion summary with named ranged criteria
If you don’t want to take care of cell references in the previous example, you can use named ranges. We will create four name ranges; Level, Course1, Course2, and Course3 for the same columns in the previous example. Just to remind you, name ranges are created in Name Manager, under Formulas tab. Final formula syntax will look like:
=COUNTIFS(Level,$H3,Course1,"<>")/COUNTIFS(Level,$H3)
Course completion summary with named ranged criteria
Named ranges in formula look like:
Level= $B$3:$B$12
Course1= $D$3:$D$12
Course2= $E$3:$E$12
Course3= $F$3:$F$12
We won’t explain formula logic since it is the same as in the previous example. You will have to use different formula under each course type as we defined separate ranges for each course type.
In cell J3 you will use the formula:
=COUNTIFS(Level,$H3,Course2,"<>")/COUNTIFS(Level,$H3)
In cell K3 you will use the formula:
=COUNTIFS(Level,$H3,Course3,"<>")/COUNTIFS(Level,$H3)
After setting adequate formulas in the first row of the resulting table, you can just drag formulas in the second row.
Please note that name ranges are very useful because you can change ranges easily in Name Manager tab. Also, you don’t have to think if cell range in the formula is corrected in each cell of the table.
Leave a Comment