Function Problems

How to Count Exactly Three Consecutive Negative Numbers in Excel

Muhammad Talha Oct 9, 2026 868 views

Question details

The user needs a way to calculate how many times exactly three negative numbers appear consecutively in a dataset, excluding streaks that are shorter or longer.

How to Count Exactly Three Consecutive Negative Numbers in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing a sequential dataset to identify and count specific contiguous streaks of negative values.
Observed behavior: Needs a formula to identify contiguous negative ranges and selectively count only those that have a length of exactly three.

Before you start

Ensure your dataset is arranged in a single continuous column or row, and note the exact range references (e.g., A2:A20) before applying the formula.

Solution 1Recommended

Use an Array Formula with FREQUENCY and IF

This method uses the FREQUENCY function combined with IF to calculate the exact lengths of negative number streaks and counts those that equal exactly three.

The FREQUENCY function is excellent for grouping data into bins. By combining it with the IF and ROW functions, we can determine the exact length of each consecutive streak of negative numbers.

The formula evaluates the rows where numbers are negative and uses the positive numbers as breakpoints (bins) to calculate the streak lengths.

Select the target cell

Click on an empty cell where you want the final count of consecutive streaks to be displayed.

Enter the array formula

Type the formula =SUM(--(FREQUENCY(IF(A2:A20<0,ROW(A2:A20)),IF(A2:A20>=0,ROW(A2:A20)))=3)) into the formula bar. Be sure to change A2:A20 to match the actual range of your data.

Execute the formula

If you are using Microsoft 365, simply press Enter. If you are using older versions of Excel, you must press Ctrl+Shift+Enter simultaneously to evaluate it properly as an array formula.

Use an Array Formula with FREQUENCY and IF

Array Formula Execution: When executed correctly in older versions, Excel will automatically surround the formula with curly braces { }, indicating it is processing as an array.

Advanced Data Analysis

Count Consecutive Values Easily in WPS Spreadsheet

WPS Spreadsheet fully supports complex array formulas, including FREQUENCY and IF combinations, allowing you to accurately count data streaks just like in Microsoft Excel.

1. Open your dataset: Launch WPS Office and open the spreadsheet containing the data you want to analyze.
2. Input the streak formula: Select a blank cell and input =SUM(--(FREQUENCY(IF(A2:A20<0,ROW(A2:A20)),IF(A2:A20>=0,ROW(A2:A20)))=3)).
3. Calculate the result: Press Ctrl+Shift+Enter to execute the array formula and instantly view the count of consecutive negative numbers.

Fully compatible with Microsoft Excel array formulas and functions.Easily handles complex data analysis without requiring VBA macros.Lightweight software with a familiar, easy-to-use interface.Free to use for everyday data management and reporting tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my array formula returning a #VALUE! error or an incorrect count?

This usually happens in older versions of Excel if you press only Enter instead of Ctrl+Shift+Enter. Ensure you confirm the formula as an array formula so Excel can evaluate the entire range properly.

How can I count streaks of consecutive positive numbers instead?

To count positive numbers, reverse the conditions in the IF statements. Use IF(A2:A20>0,ROW(A2:A20)) for the data array and IF(A2:A20<=0,ROW(A2:A20)) for the bins array in the FREQUENCY function.

Can I change the formula to count streaks of a different length?

Yes. To count a different streak length, simply change the =3 at the end of the formula to your desired number, such as =4 or =5.

Excel & Data Problems