Count Consecutive Occurrences of Values in Google Sheets

Published on

Are you looking for a way to find duplicate streaks in Google Sheets? You can use the following formula to count the consecutive occurrences of values in a dataset.

=ArrayFormula(CHOOSECOLS(QUERY(HSTACK(range, range&"|"&ROW(range)*(range<>"")-COUNTIFS(ROW(range),"<="&ROW(range), range, range), ROW(range)), "select Col1, max(Col3), count(Col2) where Col1 is not null group by Col1, Col2 order by max(Col3) label max(Col3)'', count(Col2)''"), 1, 3))

Where “range” is a single-column range containing the values you want to analyze for streaks.

Purpose

Counting consecutive occurrences of values in Google Sheets helps analyze streaks or clusters of repeated values in a dataset. This technique is useful in many real-life scenarios, such as tracking consecutive absences of employees, identifying repeated product purchases, and analyzing streaks in sports or sales data.

For example, consider a student attendance sheet with dates in one column and attendance status (Present or Absent) in another column. Applying the formula to the Present/Absent column will return the count of consecutive occurrences of Present and Absent entries.

If the dates range from 01/01/2025 to 31/01/2025 (excluding weekends) and a student was absent from 20/01/2025 to 24/01/2025, the formula will return the following result:

Present    13
Absent     5
Present    5

Now, let’s look at an example using fruit data to identify repeating groups.

Example: Counting Consecutive Occurrences in Google Sheets

Assume we have the following fruit data in column B, within the range B2:B.

Example of counting consecutive occurrences of values in Google Sheets

Formula to Count Consecutive Occurrences

To count consecutive occurrences of all fruits in one go, enter the following formula in D2:

=ArrayFormula(CHOOSECOLS(QUERY(HSTACK(B2:B, B2:B&"|"&ROW(B2:B)*(B2:B<>"")-COUNTIFS(ROW(B2:B),"<="&ROW(B2:B), B2:B, B2:B), ROW(B2:B)), "select Col1, max(Col3), count(Col2) where Col1 is not null group by Col1, Col2 order by max(Col3) label max(Col3)'', count(Col2)''"), 1, 3))

To make the formula more readable and reusable, you can use the LET function so that the range is specified only once:

=LET(range, B2:B, ArrayFormula(CHOOSECOLS(QUERY(HSTACK(range, range&"|"&ROW(range)*(range<>"")-COUNTIFS(ROW(range),"<="&ROW(range), range, range), ROW(range)), "select Col1, max(Col3), count(Col2) where Col1 is not null group by Col1, Col2 order by max(Col3) label max(Col3)'', count(Col2)''"), 1, 3)))

Formula Explanation

We use the QUERY function to calculate the consecutive occurrences of all values in Google Sheets.

Syntax:

QUERY(data, query, [headers])

Data Component:

HSTACK(range, range&"|"&ROW(range)*(range<>"")-COUNTIFS(ROW(range),"<="&ROW(range),range,range), ROW(range))

This dataset contains three columns:

  1. Actual values from the range (e.g., B2:B).
  2. Group IDs created using row numbers and COUNTIFS to identify repeating groups.
  3. Row numbers for sorting purposes.
Query data for identifying repeated groups in Google Sheets

Understanding COUNTIFS:

COUNTIFS(ROW(range),"<="&ROW(range),range,range)

This function generates a running count of items in column B. By subtracting row numbers, we identify repeating groups.

We then combine these repeating group IDs with the item itself, which forms the second column in the QUERY data.

Final Query Statement:

"select Col1, max(Col3), count(Col2) where Col1 is not null group by Col1, Col2 order by max(Col3) label max(Col3)'',count(Col2)''"
  • Selects unique items while excluding empty rows.
  • Counts the number of consecutive occurrences for each value.
  • Sorts results based on original row order using max(Col3).

Conclusion

This method efficiently finds and counts consecutive occurrences of values in Google Sheets. Whether analyzing attendance, sales trends, or streaks in any dataset, this formula provides a powerful way to detect patterns.

Sample Sheet

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.