Is there a way to count checkboxes based on column header AND sync that based on Student ID #?-CodePudding

In my Google Sheets spreadsheet, I want to calculate the number of times a student earned a checkbox for certain criteria (column headers - Row 3). Assignment Tab Image 1

In another tab, I want to be able to calculate how many times Karen (see spreadsheet) earned a checkmark in a column with the "CTE1" header and return that value to cell E3. Report Tab 1

I was able find a formula to count the entire row based on Column Header (Row 3) but it is not connected to the Student ID #, so if I sort the data it misaligns the data.

Link to Spreadsheet

Thanks in advance!

CodePudding user response：

Try

=SUMPRODUCT(ISNUMBER(MATCH(Assignments!$3:$3,{$F$1},0))*ISNUMBER(MATCH(offset(Assignments!$1:$1,MATCH(C3,Assignments!C:C,0)-1,),{TRUE},0)))

in that formula, I search for C3 within column C MATCH(C3,Assignments!C:C,0) and then offset the first line by the value minus 1