Issue creating a summary sheet based on multiple records

[KristofferA]

Hi there everyone,

I currently have a situation where I'm struggling to work out what combination of =count, =countif etc. I need to use.

In the attached, there are 2 record sheets which essentially record whether candidates have completed a course and at what level.
It is fairly self explanatory when you see it, so I need the formulas on the summary sheet to look at each individual record and count how many level 1 courses have been completed, how many incomplete, and then how many level 2 courses have been completed, and how many are incomplete.

Essentially the situation is, I have record cards for many groups of candidates, and I need to summarise how many complete and incomplete courses have been done at what level.

Hope you can help me out. Countif Example.xlsx

[stylemonk]

There you go

Your updated excel file


I did mistake in "Level 2 Complete" for record 1, please replace =COUNTIFS('Record 1'!$B$3:$B$7,1,'Record 1'!$C$3:$C$7,1)

with =COUNTIFS('Record 1'!$B$3:$B$7,2,'Record 1'!$C$3:$C$7,1)

[KristofferA]

Title changed.

[KristofferA]

Thank you stylemonk!
Brilliant!

[stylemonk]

You welcome

[KristofferA]

Actually, I've realised that the records are to be recording scores, however the summary page is to report "completedness".
Attached is the new version, however the summary page still needs to show the same thing (Count how many complete and incomplete courses have been completed for each level).

Countif Example.xlsx

[stylemonk]

As per request

Updated