Attendance Spreadsheet Conundrum

New Contributor

I am attempting to find a way to produce a list using a combination of IF, INDEX and MATCH.


The law states students must attend a class at least 90 of the time in order to receive credit. For our first semester, that means that a student will lose credit for a class (or have to make up time) after nine absences.


In the spreadsheet we are using, teachers enter a '1' if a student was present and a '0' if the student was absent. Most students have seven classes a day (unless they have late arrival or early release), so the total for each student should be '7.' Each day has its own sheet in the workbook. There are 85 days in our first semester (which ends 12/18/2020). We have almost 400 students attending school virtually; their attendance is entered on the spreadsheet. On-campus students use our regular system because their learning is synchronous.


Sample Workbook.PNG


I would like to create a formula that tells me (and spits out a list):

  • For a particular student...
  • In a particular period over the days of the semester (multiple sheets)...
  • When he/she has more than seven absences 

At seven absences, our administration begins truancy prevention measures. I've attached the above spreadsheet sample. Any help would be very much appreciated.

5 Replies



Why 85 different sheets? I know, each sheet represents a different day. But there are other ways to reflect the day (to be specific, just have a column that contains the date).


The reason I ask is that Excel excels at taking a single database and extracting the kind of data you're looking for, the kind of summary on a given student or class or..... you name it.


I don't know what the logistics are of capturing all the information currently for the students who are attending "virtually," but it sounds like something of a nightmare, however it works. 

If the ONLY thing that really matters is "unexcused absences" would it make sense to ONLY track those,, i.e., only make an entry for those. The absence of a record would mean attendance in that case, and would not require an entry.  Or do it the other way around.   I can see reasons for tracking both, for being "more granular" in your data, but I'm just trying to illustrate the sort of questions that "re-thinking" the process might entail.


Anyway, my first question if we were meeting in person would be "How willing are you to at least consider changing the way you collect data at the Input end of things?" We can deal with the Output later. What you're asking is quite easy if you were to take care of Input by means of a single sheet covering all days.



I think everyone on our campus would be willing to change this nightmarish system. However, we also need to record all of the data in case the state audits us. We also need a way to make sure the teachers are actually taking attendance - to the best of our ability - again, for potential auditing purposes.

A single sheet covering all days? What would that look like? I appreciate the response.
I spoke to my principal and to answer the question of why we have a spreadsheet for each day: it’s to keep anyone from editing the wrong day. Also we need them to take attendance for each kid during each period because otherwise we have people who will automatically choose “present” regardless of a student’s actual status. Just extra info. Thanks again.
best response confirmed by JBASS (New Contributor)



Well, it sounds like you're kind of stuck with that situation. It's entirely possible that Excel's Power Query capability could work with the 85 sheets and deliver what you want. I do not have Power Query on my Mac (or if I do, I haven't been able to access it). So if that could work, I'll hope that one of the more qualified experts around here will chime in.


When the teachers are entering the data for each day, are they working with a sheet that lists all students already, in, say, alphabetical order, or are they also expected to enter the names?

Do all teachers get their attendance reports submitted on time such that you could, each Monday, say, produce the report for all prior periods?


How often are you expected to produce that output report you talk of: is it weekly?

If so, you could just do a copy and paste of the prior seven days and start building a single comprehensive database for all days in the past. It could look something like the attached. You'll see that I moved the names to the columns following the ID, but the only other change was to add a column for date.


If that's feasible, if you could create the consolidated sheet from the individual day sheets, then my next question would be "Do you have the most current version of Excel?" If you do, there are some new functions (notably FILTER) that could come in very handy for extracting the names of students who are exhibiting dangerous numbers of absences.



The students are in alphabetical order, but the actual students often change position in the spreadsheet due to enrollments and withdrawals. The teachers do not submit attendance on time consistently for various reasons, one of which is they have even more than usual on their plates due to the pandemic adjustments at school. 


I am playing around with Power Query now and I think once I understand that, it will serve my purposes. I do have the latest version of Excel and when I wrap my head around proper use of Power Query that, too, will be helpful. Again, I appreciate you helping me crack this. Have a great day!