Others

How to Track Student Attendance and Count Sessions in Excel

Nimra Malik Oct 13, 2026 869 views

Question details

The user needs a way to track student attendance across 12 different sessions via QR code self-registration and identify students who have attended at least eight sessions.

How to Track Student Attendance and Count Sessions in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing and summarizing multi-session attendance logs collected through an online form.
Observed behavior: Needs to collect check-ins accurately, optionally prevent multiple submissions per session, and summarize exported data to find those with eight or more attendances.

Before you start

Ensure that your attendance form requires a unique student identifier, such as an Email or Student ID, to accurately calculate totals across multiple sessions.

Solution 1Recommended

Export Form Responses and Tally Attendances with COUNTIF

Use a single attendance form for all sessions, export the collected data into Excel, and calculate each student's total attendance using the COUNTIF formula.

By setting up a single form equipped with a session selection dropdown, you can consolidate all attendance data into one spreadsheet. Excel's COUNTIF function can then easily scan the records to tally how many times a specific student's ID appears.

Set up the attendance form

Create a form with required fields for Student Name, Student ID, and Session Date/Number. Generate a QR code for this form so students can scan and register.

Export data to Excel

Once sessions are completed, navigate to the form responses tab and click 'Open in Excel' to download your collected attendance data as a spreadsheet.

Create a summary worksheet

In the downloaded Excel file, create a new worksheet. In column A, list all unique Student IDs of the enrolled students.

Apply the COUNTIF formula

In column B (cell B2), enter the formula =COUNTIF(FormSheet!A:A, A2), replacing 'FormSheet!A:A' with the actual column containing Student IDs in your exported response sheet. Drag the fill handle down to apply this formula to all students.

Export Form Responses and Tally Attendances with COUNTIF

Preventing duplicate submissions: Basic forms do not natively prevent a student from submitting multiple times per session. For automated duplicate prevention and strict QR code validation, you may need to integrate Power Apps with Power Automate.

Easier Spreadsheet Management

Track and Calculate Attendance Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing tools, including full support for COUNTIF and COUNTIFS formulas, making it incredibly easy to summarize exported form responses and track student attendance.

1. Open your exported data: Launch WPS Spreadsheet and open the CSV or XLSX file containing your exported attendance logs.
2. Extract unique IDs: Copy the Student ID column to a new sheet and use the 'Remove Duplicates' feature under the Data tab to create a clean list of students.
3. Calculate attendance totals: Next to the unique IDs, use the =COUNTIF() function to calculate the total number of sessions each student attended based on the raw data sheet.
4. Highlight target attendances: Select the calculated totals, navigate to the Home tab, click 'Conditional Formatting' > 'Highlight Cells Rules' > 'Greater Than or Equal To', and enter '8' to highlight qualifying students.

Fully compatible with Microsoft Excel (.xlsx) formats and exported form data.Built-in advanced formulas like COUNTIF and COUNTIFS for instant data summarization.Free, lightweight, and fast-loading spreadsheet editor for everyday tasks.Intuitive conditional formatting to quickly highlight students meeting attendance requirements.

microsoft office alternative - wps office

Frequently Asked Questions

Can I track all 12 sessions using a single form?

Yes. You can create one master form and include a required dropdown or choice field asking students to select the current session number or date. This keeps all responses consolidated in a single spreadsheet.

How can I prevent students from scanning the QR code and submitting multiple times?

Standard form tools cannot easily restrict a single user to one response per session if the same form is used repeatedly. For strict duplicate prevention, consider using Power Apps to build a custom application, or manually remove duplicates in Excel using the 'Remove Duplicates' feature based on Student ID and Session Date.

How do I easily identify students who have attended at least 8 sessions?

After calculating the total attendances using the COUNTIF formula, select the column containing the totals, go to 'Conditional Formatting' in your toolbar, select 'Highlight Cells Rules' > 'Greater Than Or Equal To', and type 8. This will color-code the students who met the requirement.

Office Quick Solutions