How to Find Employees with 10 or More Consecutive Attendance Days in Excel
Question details
The user wants to identify employees who have logged 10 or more consecutive days of attendance using Excel formulas.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking employee attendance records to find continuous streaks of 10 or more days marked as present.
- Observed behavior
- Evaluating each attendance row dynamically to return a 'Yes' or 'No' result, or extracting the names of the employees who meet the 10-day streak criteria.
Ensure your attendance data is organized in a table layout, with employee names in the first column and their daily attendance status (such as 'P' or 'PP') spanning across the adjacent columns in the same row.
Use BYROW and LAMBDA to Check Consecutive Streaks
Utilize a dynamic array formula to concatenate the row's attendance records and check for a specific text string representing 10 consecutive presence markers.
This method uses Microsoft 365 dynamic array functions to process the entire schedule at once without needing a helper column. It combines CONCAT to merge the row's values and XMATCH with wildcards to find the streak.
Click on the cell at the top of an empty column next to your data where you want the 'Yes/No' results to appear.
Type the following formula: =LET(ten_consecutive, REPT("PP", 10), check, LAMBDA(each_row, LET(joined, CONCAT(each_row), streak, XMATCH("*" & ten_consecutive & "*", joined, 2), IF(ISNUMBER(streak), "Yes", "No"))), BYROW(schedule, check))
Replace 'schedule' with your actual data range (e.g., B2:AE50) and change 'PP' to whatever code you use for 'Present'.
Press Enter. The formula will spill down automatically, outputting 'Yes' for employees with a 10-day streak and 'No' for those without.

Calculate Streaks with a Helper Column and Filter
Calculate the maximum consecutive streak for each employee in a helper column using SCAN, then use the FILTER function to extract the names.
Track Employee Attendance Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like BYROW, LAMBDA, SCAN, and FILTER. You can easily calculate consecutive attendance streaks without worrying about compatibility issues, making HR and admin tasks highly efficient.
- 1. Open your attendance tracker: Launch WPS Spreadsheet and open your employee attendance workbook.
- 2. Apply the dynamic formula: Select the target cell and paste your preferred dynamic array formula (e.g., BYROW or FILTER).
- 3. Process the data instantly: Press Enter to instantly calculate the consecutive streaks across your entire employee roster.
- 4. Save and share seamlessly: Save your file in the standard .xlsx format to ensure seamless sharing with your colleagues.

Frequently Asked Questions
Can I use these formulas in older versions of Excel?
No, functions like BYROW, LAMBDA, SCAN, and FILTER are dynamic array functions available only in Microsoft 365, Excel 2021, or modern spreadsheet apps like WPS Office. For older versions, you might need complex legacy array formulas using FREQUENCY or VBA macros.
What if my attendance code is just 'P' instead of 'PP'?
You can easily adjust the formula. In the BYROW formula, simply change REPT("PP", 10) to REPT("P", 10). Ensure the text string perfectly matches your data entry format.
How do I handle weekends or holidays in the consecutive count?
If weekends and holidays should not break the streak, you will need to exclude those columns from your range or use a more advanced conditional formula to ignore non-working days before counting the consecutive 'Present' days.
Why does my FILTER formula return a #CALC! error?
The #CALC! error in the FILTER function occurs when no records match your criteria. In this context, it means no employee has achieved 10 or more consecutive attendance days. You can add the optional [if_empty] argument, like =FILTER(A2:A4, R2:R4>=10, "No matches"), to display a custom message instead of an error.




