'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Count Runs of Three Consecutive Values Above a Criterion in Excel
Question details
The user needs to count the number of times a sequence of at least three consecutive values in a row (e.g., B1:Z1) exceeds a specified criterion (e.g., A1).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing a row of data to identify and count specific streaks or runs of data points that meet or exceed a target threshold.
- Observed behavior
- Requires a formula combination that can accurately group consecutive cells meeting the criteria and sum the occurrences of runs that are three or more cells long.
Ensure your data is organized in a contiguous row without interfering blank cells, and note that older versions of spreadsheet software may require confirming array formulas with a specific keyboard shortcut.
Use the FREQUENCY and IF Array Formula
The most robust way to count consecutive occurrences above a threshold is by combining the FREQUENCY, IF, and SUM functions in an array formula.
This formula uses the IF function to identify column numbers where the condition is met and where it is not. The FREQUENCY function then groups these true instances based on the false instances (the interruptions in the run), calculating the length of each consecutive streak.
Click on the cell where you want the final count of consecutive runs to appear.
Type the following formula into the formula bar: =SUM(--(FREQUENCY(IF(B1:Z1>A1,COLUMN(B1:Z1)),IF(B1:Z1<=A1,COLUMN(B1:Z1)))>=3))
If you are using Microsoft 365 or modern versions of WPS Spreadsheet, simply press Enter. For older versions of Excel, you must press Ctrl+Shift+Enter to evaluate it properly as an array formula (curly braces {} will appear around it).
Click and drag the fill handle at the bottom-right corner of the cell downwards to apply this calculation to additional rows of data. The row references will adjust automatically.
Count Consecutive Values Effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports complex array formulas like FREQUENCY and IF, allowing you to seamlessly analyze consecutive runs and data trends just as you would in Microsoft Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data sequences.
- 2. Select an empty cell: Click on the cell at the end of your data row where you want to display the run count.
- 3. Enter the FREQUENCY formula: Paste the formula =SUM(--(FREQUENCY(IF(B1:Z1>A1,COLUMN(B1:Z1)),IF(B1:Z1<=A1,COLUMN(B1:Z1)))>=3)) into the formula bar.
- 4. Press Enter: Hit Enter to instantly calculate the total number of runs that meet your criterion.
Frequently Asked Questions
How do I change the formula to count runs of 5 consecutive values instead of 3?
To look for a different run length, simply change the number at the very end of the formula. Replace '>=3' with '>=5' to count streaks of five or more consecutive values.
Why does my formula return a #VALUE! error?
This usually happens in older spreadsheet software if you just press Enter. You must press Ctrl+Shift+Enter to evaluate it as an array formula. When done correctly, curly braces {} will automatically enclose the formula.
Can I use this formula for data in a column instead of a row?
Yes. If your data is organized vertically (e.g., A2:A50), you need to replace the COLUMN() function with the ROW() function in the formula, and update the ranges accordingly.
