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


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 !

    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:


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