Function Problems

How to Count the Longest Consecutive Streak Above a Value in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to find an Excel formula that calculates the longest run of consecutive values greater than 199 in a specified range without using helper columns.

Product: Excel
Device & OS: not provided
Scenario: Analyzing a dataset in Excel to find the maximum consecutive streak of numbers exceeding a specific threshold (e.g., 199) within a 2D range (A3:C22).
Observed behavior: The user needs a single-cell dynamic array formula solution to achieve this goal instead of relying on manual counting or helper columns.

Before you start

Ensure you are using a version of Excel that supports dynamic array formulas and the LAMBDA helper functions (such as Excel 365 or Excel 2021).

Solution 1Recommended

Use the SCAN and LAMBDA Dynamic Array Formula

Calculate the longest streak in a single cell using the SCAN function combined with LAMBDA to iterate through the array and track running totals.

This method uses dynamic array functions to evaluate a condition across a 2D range (A3:C22) without needing helper columns. The SCAN function keeps a running total that increments when the condition is met and resets to zero when it fails. Wrapping this in a MAX function yields the longest qualifying streak.

Select the destination cell

Click on the blank cell where you want the final maximum streak count to appear.

Input the formula

Click inside the formula bar and type the following formula: `=MAX(SCAN(0, A3:C22>199, LAMBDA(n, t, IF(t, n+1, 0))))`.

Execute the calculation

Press the Enter key on your keyboard to execute the formula. The cell will immediately display the maximum consecutive run.

Understanding the logic: The expression 'A3:C22>199' creates an array of TRUE/FALSE values. The LAMBDA function receives the accumulated value 'n' and current value 't'. If 't' is TRUE, it adds 1 to the streak; if FALSE, it breaks the streak by resetting 'n' to 0.

Advanced Formulas in WPS Spreadsheet

Calculate Consecutive Streaks Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful data analysis tools and is highly compatible with advanced Excel formulas. You can use dynamic array functions to quickly solve complex calculations like finding data streaks without cluttering your workbook with helper columns.

1. Open your dataset in WPS: Launch WPS Spreadsheet and open the workbook containing your numerical data range.
2. Apply the array formula: Select a blank cell and input the combination formula: `=MAX(SCAN(0,A3:C22>199,LAMBDA(n,t,IF(t,n+1,0))))`.
3. Get instant results: Press Enter to calculate the longest consecutive streak instantly without adding any extra columns to your layout.

Fully compatible with Microsoft Excel formulas and array functionsEasily handle complex logical queries like streak counting using SCAN and LAMBDALightweight application that runs smoothly on Windows, Mac, and mobile devicesFree to use with a familiar, user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I change the threshold value in the formula to a dynamic cell reference?

Yes, you can replace the hardcoded '199' in the formula (A3:C22>199) with an absolute cell reference, such as A3:C22>$E$1. This allows you to update the threshold dynamically by just changing the value in cell E1.

Why does my formula return a #NAME? error?

The #NAME? error typically occurs if your spreadsheet software version does not support the newly introduced SCAN or LAMBDA functions. Ensure you are using a modern version like Excel 365, Excel 2021, or the latest updated version of WPS Office.

How can I count the streak of values less than a specific number instead?

Simply change the logical operator in the formula array. For example, to find the longest streak of values less than 50, change the condition portion of the formula to A3:C22<50.

Excel & Data Problems