Formula Errors

How to Count 91 Consecutive Blank Cells in Excel for Attendance

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs an Excel formula to count occurrences of exactly 91 consecutive blank cells in a row to apply a perfect attendance point deduction.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking employee attendance and deducting one point for every qualifying streak of 91 blank cells across the range F14:CS14.
Observed behavior: A calculation is required to accurately detect and sum the instances where a run of consecutive blanks reaches exactly 91 days.

Before you start

Ensure you are using Microsoft 365 or a compatible version of Excel that supports advanced dynamic array functions such as SCAN and LAMBDA.

Solution 1Recommended

Use Microsoft 365 Dynamic Array Formula

Utilize the SCAN and LAMBDA functions in Excel to iterate through the row and accurately count consecutive blanks within the specified range.

This modern formula approach creates a running total that increments for every blank cell and resets to zero when a non-blank cell is encountered. It then counts how many times that running total hits exactly 91.

Select the target cell

Click on the cell where you want the attendance deduction to be displayed, such as NU14.

Enter the SCAN and LAMBDA formula

Type the formula: =-SUM(--(SCAN(0,F14:CS14,LAMBDA(a,v,IF(v="",a+1,0)))=91)) in the formula bar.

Apply the calculation

Press the Enter key. The formula will calculate a -1 deduction for every time the consecutive blank count reaches exactly 91 days.

Handling multiple runs: If a streak longer than 91 days (e.g., 182 days) should produce multiple deductions, you will need to adjust the logical test to check for multiples of 91 rather than just equaling 91.

Solution 2

Create a VBA User Defined Function (UDF)

If you are using an older version of Excel that lacks dynamic array functions, a custom VBA script can manually iterate through the range to count the streaks.

Manage Complex Attendance Trackers with WPS Spreadsheet

Handle advanced array formulas, complex logic, and daily attendance tracking smoothly using WPS Spreadsheet, a lightweight and powerful alternative for data analysis.

1. Open your attendance sheet: Launch WPS Spreadsheet and open your existing .xlsx attendance tracker file.
2. Select the deduction cell: Click on the specific cell in the row where you want to calculate the attendance points.
3. Input the array calculation: Type your logical counting formula into the formula bar to evaluate the blank cells across the row.
4. Fill down for all employees: Click and drag the small square fill handle at the bottom-right of the cell downwards to apply the calculation to the rest of the staff.

Highly compatible with Microsoft Excel formulas and .xlsx filesSupports robust logical, statistical, and mathematical functionsFree to use with a clean, familiar interfaceSeamless migration of your existing attendance sheets

microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula use double hyphens (--)?

The double hyphen, also known as a double unary operator, is used to convert the TRUE or FALSE boolean values generated by the logical test into 1s and 0s. This allows the SUM function to mathematically add them up.

Can I count consecutive blanks without using SCAN and LAMBDA?

Yes, but in older Excel versions it requires complex array formulas combining FREQUENCY, IF, and ROW functions. Using a VBA User Defined Function (UDF) is generally a more reliable workaround for older versions.

Will a 182-day blank streak deduct two points using this exact formula?

No. The formula `=-SUM(--(SCAN(...)=91))` specifically counts the exact moment the running total hits 91. To get two deductions for 182 days, the logic must be adjusted to account for multiples of 91.

Excel & Data Problems