'Count Number of Consecutive Occurrence of values in Google sheets
This is the original data, all the data are of two kinds: red and black. And then, I want to study the occurrence of all the blocks. The result will be like this:
It means the first streak of red(from index 1 to 3) has a length of 3, and the second streak which is black(from index 4 to 5) has a length of 2...
I want to find out an elegant way to calculate it but in sheets, it's very hard. COUNTIF and ROWS all can't perfectly resolve this problem.
Do you have an elegant way?
Solution 1:[1]
Not sure it's elegant, but you could add two helper columns, the first column checks if the record has changed, and the second counts until the next change using a MATCH. Note you'd need an extra "TRUE" below the last record to catch the last streak. Then you can use FILTER to show the blocks and occurances.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | bn_ln |