How to Count Excel Attendance Between Two Dates with SUMPRODUCT
Question details
The user needs to count specific attendance marks for individuals between a past date and today using data from a separate worksheet, and then determine if they meet a specific attendance threshold.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking employee or student attendance across different sheets and checking if they meet a minimum attendance requirement within a specific date range.
- Observed behavior
- The user wants to calculate the dynamic attendance count based on changing event dates and accurately identify whether the person qualifies based on the counted marks.
Ensure that all date values in your worksheets are formatted as standard Excel dates rather than text, and that the names in your summary sheet exactly match the names in your attendance data sheet.
Use SUMPRODUCT, INDEX, and MATCH to Count Attendance
Combine SUMPRODUCT with INDEX and MATCH to look up a person's name across another sheet and count specific attendance marks within a dynamic date range.
This formula dynamically locates the correct row for a person, checks the dates against your specified range (e.g., from a past event date up to today), and counts the specific attendance marker, such as "P" for Present.
Confirm the ranges on your attendance sheet (e.g., Sheet2). Assume dates are in B3:R3, names in A4:A7, and attendance marks in B4:R7.
In your summary sheet, click on the cell where you want the attendance count to be displayed.
Type the formula: =SUMPRODUCT(--(INDEX(Sheet2!$B$4:$R$7,MATCH($C2,Sheet2!$A$4:$A$7,0),)="P")*--(Sheet2!$B$3:$R$3>=$D2)*--(Sheet2!$B$3:$R$3<=TODAY())) where $C2 is the person's name and $D2 is the start date.
Press Enter to calculate the total attendance marks for that person between the specified dates.
Apply an IF Formula for Qualification
Use an IF statement to evaluate the calculated attendance count against your required threshold to see if the person qualifies.
Track Attendance Easily with WPS Spreadsheet
You can seamlessly build and calculate complex attendance formulas like SUMPRODUCT and INDEX/MATCH using WPS Spreadsheet. It offers full compatibility with standard Excel functions, making data management highly efficient.
- 1. Open your Workbook: Launch WPS Spreadsheet and open your attendance tracking file.
- 2. Apply Functions: Use the built-in formula bar to insert SUMPRODUCT, INDEX, and MATCH effortlessly.
- 3. Calculate and Format: Input your date parameters and use the drag-to-fill feature to copy formulas across your roster seamlessly.
Frequently Asked Questions
Why is my SUMPRODUCT formula returning an error or zero?
This usually happens if the dates in your header row or your criteria cells are stored as text instead of standard date formats. Check your cell formatting and ensure they are formatted as a Date.
Can I use this formula if the names on the two sheets are in a different order?
Yes, the MATCH function nested inside the formula ensures that it searches for the specific name regardless of the row order on the attendance data sheet.
How do I count multiple attendance marks, like 'P' (Present) and 'L' (Late)?
You can modify the formula to include an OR logic array, or simply add two SUMPRODUCT formulas together within the same cell—one checking for "P" and the other for "L".
