User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

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.

 

 

Count Max Consecutive Occurrences

  

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