Determine number of learners who have completed different stages of multiple online courses

{ 4 Comments }

Here is a sample dataset of learners who have cleared different stages of multiple courses on offer within an Organisation:

Learner Stage completed Course
Bill Stage 1 Public Speaking
Bill Stage 2 Public Speaking
Bill Stage 3 Public Speaking
Susan Stage 1 Effective Communication
Bob Stage 1 Public Speaking
Bob Stage 2 Public Speaking
Sheila Stage 1 Effective Communication
Sheila Stage 2 Effective Communication
Sheila Stage 3 Effective Communication
Frank Stage 1 Effective Communication
Frank Stage 2 Effective Communication
Henry Stage 1 Public Speaking
Henry Stage 2 Public Speaking
Bill Stage 1 Effective Communication
Bill Stage 2 Effective Communication

From this sample dataset, one may want to know how many participants have completed each stage of these multiple courses.  The expected result is shown below:

Row Labels Stage 1 Stage 2 Stage 3
Effective Communication 1 2 1
Public Speaking 2 1
Grand Total 1 3 2

In this workbook, I have shared 2 solutions – one using formulas and the other using the Power Query & PowerPivot.

Leave a Comment

Your email address will not be published.

*

  • There is a much shorter version of the Excel formula which does not require helper columns or rows !

    =SUM(($C$2:$C$16=$A20)*($B$2:$B$16>=(B$19)))-SUM(IFERROR(TRANSPOSE(ROW(INDIRECT(“2:”&COLUMNS(B$1:$C1)+1)))*(C20:$E20),0))
    Array enter it in B20 and copy paste it over B20:D21
    You can see the correct results only when you copy paste it
    The DAX formula can also be optimized

  • Here is a less clever, but far shorter and more efficient version of the Excel formula. As with Sam’s solution, first array enter it in B20 and copy/paste it in all of them for it to work:

    =SUM(IF($B$2:$B$16&$C$2:$C$16=B$19&$A20,1))-SUM(C$20:D$20)

    As a side note, your total above for Stage 2 seems to be off.