Are you struggling to get the count of consecutive occurrences of values in a column in Google Sheets? It may seem simple if the list is sorted, but what about an unsorted list? Don’t worry, we’ve got you covered with this new Google Sheets tutorial.
Count of Consecutive Occurrences of Values in a Sorted Column
If you have a sorted list of values in column B, you can use either the Query or Countif function for the calculation. Let me show you how!
Countif Approach:
To use the COUNTIF function, first, you need to get the unique values by inserting the following formula in cell D2:
=unique(filter(B2:B,B2:B<>""))
Next, the following formula in cell E2 will return the count of consecutive occurrences of values in column B:
=ArrayFormula(if(D2:D="",,countif(B2:B,D2:D)))
Query Approach:
If you prefer using the QUERY function, you can use the following formula:
=query(B2:B,"Select B,count(B) where B is not null group by B label count(B)''")
The QUERY function is an elegant way to deal with such problems because of its grouping ability.
Count of Consecutive Occurrences of Values in an Unsorted Column
Now, what if you have an unsorted list of values in column B and you want to keep the order without sorting? No worries, we have a solution for you!
To distinguish similar sets of values and count their occurrences separately, you can use either a Countif-based or Query-based approach.
Countif-Based Approach:
In cell D2, use the following formula to 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)
Then, the formula in cell E2 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 using the QUERY function, use the following formula:
=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 behind both the Countif and Query-based formulas is the running count, which helps distinguish each set of values. Here’s how it works:
- Use the following formula to get the running count of occurrences of the fruits (values):
=ArrayFormula(if(B2:B="",,COUNTIFS(ROW(B2:B),"<="&ROW(B2:B),B2:B,B2:B)))
- 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)))
- Join the output of step #2 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)))
By following these three steps, we can distinguish the values and use them to get the count of consecutive occurrences of values in an unsorted list in Google Sheets.
Countif-Based Approach (Generic Formula):
To use a generic formula approach, insert the following formula in cell D2:
=index(trim(split(unique(step_3_formula),"❤")),0,1)
This formula will provide you with the unique values, removing the delimiter “❤ ❤”.
Query-Based Approach (Generic Formula):
To use the generic formula approach with the QUERY function, use the following formulas:
Query_1:
=ArrayFormula(query({B2:B,<strong>step_3_formula</strong>,row(B2:B)},"Select Col1,max(Col3),count(Col2) group by Col1,Col2 order by max(Col3) label max(Col3)'',count(Col2)''"))
Then, remove unwanted columns using an outer QUERY:
=ArrayFormula(query(<strong>query_1</strong>,"Select Col1,Col3 where Col1 is not null"))
That’s all there is to it! Now you can easily get the count of consecutive occurrences of values in Google Sheets. If you’d like to learn more about Google Sheets and other helpful tutorials, visit Crawlan.com. Enjoy!
Example Sheet 12123
