User Rating: 5 / 5

Count Maximum Number of Consecutive Occurrences of a value in a Column

Count Maximum Number of Consecutive Occurrences of a value in a Column

Cell C3 formula Counts Maximum Number of Consecutive Occurrences, of a value in a Column - Maximum Consecutive Occurrences of the number 5 (cell B3 value) in column A.

-----------------------------------------------------------------------------------------------

Cell C3 formula Counts Maximum Consecutive Occurrences of the number 5 (cell B3 value) in column A.

Cell C3 formula:

=MAX(FREQUENCY(IF( A2:A30=B3, ROW(A2:A30)), IF(A2:A30<>B3, ROW( A2:A30))))

Explanation - Cell C3 formula (Array Formula - Ctrl+Shift+Enter):

1) IF(A2:A30=B3, ROW(A2:A30)): returns an array of numbers and FALSE values - {FALSE; FALSE; FALSE; 5;FALSE; FALSE; 8;9; 10;11; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; 19;20; 21; FALSE;…} - the numbers indicate the row numbers of cells where column A value = B3. This is the data_array argument of the FREQUENCY function.

2) IF(A2:A30<>B3, ROW(A2:A30)): returns an array of numbers and FALSE values - {2;3;4; FALSE; 6;7; FALSE; FALSE; FALSE; FALSE; 12; 13;14; 15; 16;17; 18; FALSE;…} - the numbers indicate the row numbers of cells in column A which do not contain B3 value. This is the bins_array argument of the FREQUENCY function against which the Frequency of each value in 1) above is determined. Note that the FREQUENCY function enables calculating the FREQUENCY of a value ONLY ONCE ie. if a value occurs multiple times, say twice, then its Frequency is returned as 2 but the value is considered only once at the position of its 1st occurrence.

3) FREQUENCY(IF( A2:A30=B3, ROW(A2:A30)), IF(A2:A30<>B3, ROW(A2:A30))): returns an array of numbers and zeros - {0;0; 0;1; 0;4; 0;0; 0;0; 0;0; 3;0; 0;0; 0;0; 0;0; 0;0} - the numbers indicate the number of consecutive cells which contain B3 upto the row number following these consecutive cells. Example, the number 1 indicates that there is only 1 cell (row number 5 - cell A5) with occurrence of B3 value upto row number 6 (bins_array value) ie. there is only one number 5 in the data_array which is less than the value 6 in bins_array. The number 4 indicates that there are 4 consecutive cells (row numbers 8, 9, 10 & 11 - cells A8, A9, A10 & A11) upto row number 12 (bins_array value) ie. there are 4 numbers (8,9, 10,11) in the data_array which are greater than bin_array value 6 and less than value 12. Using the MAX function returns the maximum number of consecutive cells containing cell B3 value.

-----------------------------------------------------------------------------------------------

Related Formulas - illustrated in the Ebook of Excel Formulas.

Cell C4 Formula: Counts Number of Groups, of Consecutive Occurrences (minimum one value) of the number 5 (cell B3 value) in column A - Array formula (Ctrl+ Shift+ Enter).

Cell C6 Formula: Returns Largest number of consecutive non-blank cells in column A - Array formula (Ctrl+ Shift+ Enter).

Cell C7 Formula: Counts Number of Groups (minimum one blank cell), of Consecutive non-blank cells in column A - Array formula (Ctrl+ Shift+ Enter).