Formula Errors

How to Count Consecutive X and L Entries as One in Excel

Chanuka Geekiyanage Oct 9, 2026 868 views

Question details

The user needs to count specific text entries (X and L) in an attendance worksheet while treating consecutive identical entries as a single occurrence.

How to Count Consecutive X and L Entries as One in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating attendance, leaves, or specific event occurrences across a row of dates where continuous identical statuses should only be counted as one block.
Observed behavior: Standard counting functions like COUNTIF count every individual instance of 'X' or 'L', overestimating the number of unique occurrence blocks.

Before you start

Ensure your attendance data is arranged in a continuous row or column without merged cells, and verify that no trailing spaces exist within the cells containing 'X' or 'L'.

Solution 1Recommended

Use an Array Formula to Count Unique Blocks

This approach compares each cell with the adjacent cell to ensure consecutive identical entries are only counted once at the end of the block.

By using the SUM function combined with boolean logic, you can check if a cell contains 'X' or 'L' and whether it is different from the cell immediately following it. This effectively counts the last cell of each consecutive block, giving you the total number of unique occurrences.

Select the target cell

Click on the cell where you want the final count of occurrences to appear.

Input the array formula

Type the formula: =SUM(--(((A2:Z2="X")+(A2:Z2="L"))>0)*--(A2:Z2<>B2:AA2)). Adjust A2:Z2 to match your actual data range.

Apply the calculation

Press Enter to calculate the result. If you are using an older version of Excel, you may need to press Ctrl+Shift+Enter to evaluate it as an array formula.

Use an Array Formula to Count Unique Blocks

Understanding Range Offsets: Notice that the second range (B2:AA2) is offset by one column to the right of the first range (A2:Z2). This intentional offset allows the formula to compare each cell with its immediate right neighbor.

Powerful Spreadsheet Solution

Count Consecutive Entries Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, allowing you to track attendance and evaluate consecutive entries seamlessly. Enjoy a highly compatible and lightweight spreadsheet tool for all your complex data tracking needs.

1. Open your file in WPS: Launch WPS Spreadsheet and open your attendance tracking document.
2. Select the total cell: Click on the blank cell at the end of the row where the total count should be displayed.
3. Paste the formula: Paste =SUM(--(((A2:Z2="X")+(A2:Z2="L"))>0)*--(A2:Z2<>B2:AA2)) into the formula bar and adjust the ranges to fit your data.
4. Calculate: Press Enter to instantly get the accurate count of unique X and L blocks.

Fully compatible with Microsoft Excel formulas, including advanced array logic.Free, lightweight, and fast performance for everyday attendance management.Familiar user interface ensuring a seamless migration from other spreadsheet tools.

microsoft office alternative - wps office

Frequently Asked Questions

How can I count different letters instead of X and L?

You can easily modify the formula by replacing 'X' and 'L' with your desired text characters. For example, to count 'A' and 'B', change the formula segment to ((A2:Z2="A")+(A2:Z2="B")).

Why is my formula returning an error or 0?

This usually happens if the two ranges are not the exact same size. Ensure that if your first range spans 26 columns (e.g., A2 to Z2), the offset range also spans exactly 26 columns (e.g., B2 to AA2).

Does this formula work for vertical columns instead of horizontal rows?

Yes, you can adapt it for vertical data by offsetting the rows instead of the columns. For example, if your data is in A2:A30, your formula would use A2:A30 compared against A3:A31.

Excel & Data Problems