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