# Count attendance percentage for top 10 specific events

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.

``````=SUMPRODUCT(QUERY(QUERY(QUERY(TRANSPOSE({\$C\$3:\$3;C4:4}),
• Thank you very much, this worked as it should :) The only thing I needed to change was the order - in my case the events are added on the left, so instead of `"limit 10 offset "&(COUNT(\$C\$3:\$3)-10)` I needed only `"limit 10"`. Mar 13 '19 at 23:47