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

**Related Links:**

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

**Count Number of Groups of Consecutive Occurrences of a value in a Column; Count Maximum Number of Consecutive Occurrences of a value in a Column**

**Column C formulas Count Number of Groups of Consecutive Occurrences / Maximum Number of Consecutive Occurrences, of a value in a Column.**

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

**Cell C4: Count Number of Groups, of Consecutive Occurrences (minimum one value) of the number 5 (cell B3 value) in column A.**

**Cell C6: Largest number of consecutive non-blank cells in column A.**

**Cell C7: Count of Number of Groups (minimum one blank cell), of Consecutive non-blank cells in column A.**

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

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

**"Open Image in New Tab" for a full and clear view.**

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

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

**Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas**.