How to organise Attendance data if it is in the single cell.

Occasional Visitor

Hi, for our NGO, we get the students attendance data in a particular format from the App.  The rows have following values - 

 

Date - Subject - Volunteer - Students' names & Present/Absent (this student data in single cell).

 

I could definitely segregate the data from the single cell to multiple cells in the same row. BUT now how do I make a report where I can see if a student was present or absent for a subject date-wise or at least the total number of days a particular student has attended in that month, subject-wise.

 

Little Urgent. PLEASE HELP

1 Reply

@urpreeto 

=SUMPRODUCT(($E$5:$E$12=D$15)*ISNUMBER(SEARCH($C16,$G$5:$G$12))*ISNUMBER(SEARCH($C$14,$G$5:$G$12))*(MONTH($D$5:$D$12)=$B$14))

Does this show what you want to do?

subject student.JPG