Get the Count of Consecutive Occurrences of Values in Google Sheets

Published on

At first glance, it seems pretty simple to get the count of consecutive occurrences of values in a column in Google Sheets.

It’s true if we have a sorted list because we can use Query or Countif for the calculation.

But what about an unsorted list and still wanting the count of consecutive occurrences of values in that list?

You can learn that in this new Google Sheets tutorial.

Count of Consecutive Occurrences of Values in a Sorted Column

In the following example, the values are in the cell range B2:B and sorted.

In this, we can use Query or Countif as follows.

Count of Consecutive Occurrences of Values in Sorted List
image # 1

Countif Approach:

If you use COUNTIF, first, get the unique values by inserting the following UNIQUE and FILTER combination formula in cell D2.

=unique(filter(B2:B,B2:B<>""))

The following E2 formula will return the count of consecutive occurrences of values in B2:B.

=ArrayFormula(if(D2:D="",,countif(B2:B,D2:D)))

Query Approach:

I suggest the following QUERY for this type of count of consecutive occurrences of values in Google Sheets.

=query(B2:B,"Select B,count(B) where B is not null group by B label count(B)''")

It’s one of the elegant ways of dealing with such problems because of its grouping ability.

Count of Consecutive Occurrences of Values in an Unsorted Column

This time we have unsorted values in column B and want to keep that order. We don’t want to sort the list.

Count of Consecutive Occurrences of Values in Unsorted List
image # 2

To explain further, we have two sets of the value “Pear”: one in rows 11-13 and the other in rows 15-16. We should find a way to distinguish them.

So that we can group and count the occurrences of them separately.

So the count of occurrences of the value “Pear” will be 3 for the first set and 2 for the second.

We will learn how to distinguish similar sets of values in the formula anatomy part. Before that, here are the formulas.

Countif-Based Approach:

In cell D2, the following formula will return the item names.

=index(trim(split(unique(B2:B&"❤ ❤"&if(B2:B="",,ROW(B2:B)-COUNTIFS(ROW(B2:B),"<="&ROW(B2:B),B2:B,B2:B))),"❤")),0,1)

The following E2 formula will return the count of consecutive occurrences of values.

=ArrayFormula(if(D2:D="",,countif(B2:B&"❤ ❤"&if(B2:B="",,ROW(B2:B)-COUNTIFS(ROW(B2:B),"<="&ROW(B2:B),B2:B,B2:B)),unique(B2:B&"❤ ❤"&if(B2:B="",,ROW(B2:B)-COUNTIFS(ROW(B2:B),"<="&ROW(B2:B),B2:B,B2:B))))))

Query-Based Approach:

If you prefer Query, insert the following formula in cell D2.

=ArrayFormula(query(query({B2:B,B2:B&"❤ ❤"&if(B2:B="",,ROW(B2:B)-COUNTIFS(ROW(B2:B),"<="&ROW(B2:B),B2:B,B2:B)),row(B2:B)},"Select Col1,max(Col3),count(Col2) group by Col1,Col2 order by max(Col3) label max(Col3)'',count(Col2)''"),"Select Col1,Col3 where Col1 is not null"))

Anatomy of the Formula

The main logic in both the Countif and Query-based formulas is the same. It’s running count.

It helps us to distinguish each set of values. Do you know how?

Please see the below steps.

Distinguish Each Sets of Values
image # 3

1. The following formula can return the running count of occurrences of the fruits (values).

=ArrayFormula(if(B2:B="",,COUNTIFS(ROW(B2:B),"<="&ROW(B2:B),B2:B,B2:B)))

2. Then deduct the row numbers from this output.

=ArrayFormula(if(B2:B="",,ROW(C2:C)-COUNTIFS(ROW(B2:B),"<="&ROW(B2:B),B2:B,B2:B)))

3. Join the step # 2 output with the values (fruit names).

=ArrayFormula(B2:B&"❤ ❤"&if(B2:B="",,ROW(B2:B)-COUNTIFS(ROW(B2:B),"<="&ROW(B2:B),B2:B,B2:B)))

The above are the main three steps. We have distinguished the values in the third step. Please check the item “Pear” in column E (image # 3).

Now we can use this third formula output to get the count of occurrences of consecutive values in an unsorted list in Google Sheets.

Countif-Based Approach (Generic Formula):

We will use the below generic formula approach in cell D2 (please scroll up and see image # 2).

=index(trim(split(unique(step_3_formula),"❤")),0,1)

It’s like, unique the step # 3 formula output and then SPLIT and INDEX to remove the delimiter, i.e., “❤ ❤”

What about the E2 formula?

It’s just a Countif formula where the range is the step # 3 formula, and the criterion is unique(step_3_formula).

Query-Based Approach (Generic Formula):

It’s a nested Query formula where the step #3 formula plays a key role.

We will use the following Query to get the count of consecutive occurrences of the fruits.

Query_1:

=ArrayFormula(query({B2:B,step_3_formula,row(B2:B)},"Select Col1,max(Col3),count(Col2) group by Col1,Col2 order by max(Col3) label max(Col3)'',count(Col2)''"))

But it would return some unwanted columns.

Using an outer Query, we will remove those unwanted columns.

=ArrayFormula(query(query_1,"Select Col1,Col3 where Col1 is not null"))

That’s all. Thanks for the stay. Enjoy!

Example Sheet 12123

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.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.