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.
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.
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.
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!
