I need to find a way to group a set of values say >=5, and then count how many groups there are, as there are large spots of values that are <5 in between each group in excel. The amount of data between each groups fluctuates, and there are over 7000 cells of data so counting them by hand is both time consuming and non-efficient.

CodePudding user response：

The classic way of doing these is to use Frequency to group row numbers where values >=5 are present into bins separated by row numbers where values <5 are present then count the number of non-empty bins:

=SUM(--(FREQUENCY(IF(C2:C22>=5,ROW(C2:C22)),IF(C2:C22<5,ROW(C2:C22)))>0))