0

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.

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

0

| improve this answer | |
  • 1
    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". – Sergey Kudriavtsev Mar 13 '19 at 23:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.