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.

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

• Hi,

Thank you for sharing that much more efficient formula.

2. UniMord

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.

• Hi,

Thank you for sharing your knowledge/feedback.