Count specific columns based on multiple criteria and ranges of different sizes

Question

I'm working with the following table:

I need a formula I can use on a different sheet that counts how many classes were cancelled for each of the groups like:

GE COL INT MIX W     2
GE COL INT 1 W       0

The criteria to decide if a class was cancelled or not is to type "C" in one of the columns for the class (i.e. January 2, meaning second class in January). In the example table you can see that the 4 people are part of 2 groups, 2 per group; if I write C for 2 of them in the same group I want the macro to only count 1 cancelled class instead of counting both "C"s.

All of the space where the "C"s will be written (Month columns) is a named range = "Attendance" and there's also a named range for the names of all groups = "Groups".

I'm using the following simple formula:

=COUNTIFS(METRICS!F:F,H5,Attendance,"C")

METRICS!F:F Refers to the sheet where the table I just showed you is and F is the column where the levels are. H5 is the cell in the other sheet where I'm comparing the group name to make sure it's the same. Attendance is the range where I'll be writing "C".

However, I get the error:

Array arguments to COUNTIFS are of different size.

And that would count all "C"s, not only the ones I need.

Answer 1

A simple solution would be to add a column after the group name that counts absences, and then use UNIQUE() over the group names and absence count to get a unique list. eg.

Alternately, to do this without the extra column, first create a unique list of group names with =UNIQUE(Groups).

Then add a named range called GroupAttendance that includes the ranges of both Groups and Attendance.

Lastly, fill the column next to the group names with:

=COUNTIF(FILTER(UNIQUE(GroupAttendance), UNIQUE(Groups) = A2), "C")

eg.