Course completion status summary

★ 20 minutes read

In this tutorial, you will learn how to summarize course completion data. Idea is to track in separate table course type completion for each participant. If the course is completed in the cell should be displayed “Completed”, otherwise cell should remain blank. You can do this by using the combination of IF and COUNTIFS formula defined in a cell G3 with the following syntax:

=IF(COUNTIFS($B$3:$B$10,$F3,$C$3:$C$10,G$2),"Completed","")

  •    Course completion status summary
  •    Completion of a course status summary using named range   
  1.  Course completion status summary

As we have already mentioned, we want to summarize course completion status summary data for each participant. If the course is completed in a cell will be the message “Completed”. Let’s look in the example below in formula syntax and tables with data. Formula syntax for solving this problem is:

=IF(COUNTIFS(range1, criteria1, range2, criteria2), [value_if_true], [value_if_false])

=IF(COUNTIFS($B$3:$B$10,$F3,$C$3:$C$10,G$2),"Completed","")

 

Course completion status summary

Let’s go through formula syntax step by step. First, we will explain the COUNTIFS formula part. In this function, we have two criteria. Criteria1 is the participant defined in range1, column “Name”. Criteria2 is course type with course completions dates in column “Course type”. Column “Course type is range2. The result of this formula part will be number 1, if the participant completed the course, and zero if did not.

= COUNTIFS(range1, criteria1, range2, criteria2)

= COUNTIFS($B$3:$B$10,$F3,$C$3:$C$10,G$2)

The second part of the formula syntax is IF function. We use this function to populate the cell with the string “Completed” if the participant completed the course. If the condition is false, we will leave cell blank.

=IF(logical_test, [value_if_true], [value_if_false])

=IF(COUNTIFS($B$3:$B$10,$F3,$C$3:$C$10,G$2),"Completed","")

Logical test is the COUNTIFS function. IF function will treat any positive result from COUNTIFS function as a TRUE value and we will display the message “Completed” under [value_if_true] part of IF function. If COUNTIFS result is zero, the result of IF function is FALSE and cell will remain blank.

 

  1.     Completion of a course status summary using named range

We can use named ranges instead of cell ranges defined in COUNTIFS part of the formula. Course completion status summary will be the same, while COUNTIFS formula syntax will be with named ranges. We will create two name ranges, Name, and Course_Type 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:

=IF(COUNTIFS(named range1, criteria1, named range2, criteria2), [value_if_true], [value_if_false])

 =IF(COUNTIFS(Name,$F3,Course_Type,G$2),"Completed","")

 

Completion of a course status summary using named range

Named ranges in formula look like:

Name= $B$3:$B$10

Course_Type= $C$3:$C$10

Formula logic is the same as in the previous example. We have just replaced cell ranges with named ranges. Name ranges are very useful because you can change ranges easily in Name Manager tab. You can add participants and course completion data in the source database, and adjust easily ranges of column Name and Course Type in Name Manager.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar