Count attendance percentage for top 10 specific events

Question

Data

I have an attendance sheet for multiple events and for multiple people. The sheet looks like this (mock data):

X |    A     |   B     |   C   |   D   |   E   |   F   |   G   | ...
1 |          |         |       |       |       |       |       | ...
2 |    X     | Att. %  | Evt 1 | Evt 2 | Evt 3 | Evt 4 | Evt 5 | ...
3 | Special? |     -   |  1    |  0    |  0    |  1    |  0    | ...
  --------------------------------------------------------------------
4 | Person 1 |  60%    |  1    |  1    |       |  1    |       | 
5 | Person 2 |  20%    |       |       |  1    |       |       |
    ...

So, I'm counting the total attendance of each person by using the following formula, for example B4: =SUM(OFFSET(A4,0,2,1,100))/COUNTIF(OFFSET(A$2,0,2,1,100),"<>")

I also have a formula for counting the attendance rate only for the events marked as "Special" in row 3, using SUMPRODUCT, for example for row 4 it is: =SUMPRODUCT(OFFSET(A$3,0,2,1,100),OFFSET(A4,0,2,1,100))/SUM(OFFSET(A$3,0,2,1,100))

However, now I need to change this formula so that it only counts attendance of the last 10 "Special" events. The events are added to the table by inserting a new column at C and moving the rest to the right, so I effectively need to count attendance for the leftmost 10 events marked as "Special".

Any clues? I'd be grateful even for a small hint regarding the function(s) I could use since my experience with Google Sheets is not that broad.

Answer 1

so that it only counts attendance of the last 10 "Special" events

=SUMPRODUCT(QUERY(QUERY(QUERY(TRANSPOSE({$C$3:$3;C4:4}),
 "where Col1=1"),
 "limit 10 
  offset "&(COUNT($C$3:$3)-10)),
 "select Col2"))/10