In sheet AttendanceByGivenDate, forula in cell B2 is:
=transpose(filter(SourceData!A:A,filter(SourceData!A:F,SourceData!A$1:F$1=A2)))
In sheet AttendanceByGivenName, formula in cell B2 is:
=ArrayFormula(if(index(SourceData!B:F,match(A2,SourceData!A:A,0),0),SourceData!B$1:F$1,"No"))
In sheet ClassesByPersonBetween2Dates, formula in cell D2 is:
=count(if(SourceData!B$1:F$1>=B2,if(SourceData!B$1:F$1<=C2,index(SourceData!B:F,match(A2,SourceData!A:A,0),0),"")))
In sheet AbsencesByPersonBetween2Dates, formula in cell D2 is:
=countblank(filter(filter(SourceData!A:F,SourceData!A$1:$F$1>=B2,SourceData!A$1:F$1<=C2),SourceData!A:A=A2))
In sheet AbsencesByPersonBetween2Dates, formula in cell D2 is:
=countblank(filter(filter(SourceData!A:F,SourceData!A$1:$F$1>=B2,SourceData!A$1:F$1<=C2),SourceData!A:A=A2))
I think the third formula is going to be particularly useful.
ReplyDelete=count(if(SourceData!B$1:F$1>=B2,if(SourceData!B$1:F$1<=C2,index(SourceData!B:F,match(A2,SourceData!A:A,0),0),"")))
in order to calculate a penalty for non attendance do you have any thoughts on how I could adapt this to count the number of blank cells instead?
Hi Paul:
ReplyDeleteI have added a sheet AbsencesByPersonBetween2Dates to count the number of blank cells ... with the following formula in cell D2:
=countblank(filter(filter(SourceData!A:F,SourceData!A$1:$F$1>=B2,SourceData!A$1:F$1<=C2),SourceData!A:A=A2))
Let me know if this is what you are looking for.