User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Count Number of Rows with occurrence of atleast one 1

 

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 Rows in a Multi Column-Row Array, having occurrence of atleast one 1

 

 

Column H formulas Count the Number of Rows with occurrence of atleast one 1.

 

If a row in the range A2:F20 contains atleast a single 1, it is considered and counted. Even if a row contains multiple 1s, it is counted as one. 

 

Cell H4 formula: Uses Frequency Function - Enter formula as an array formula (Ctrl+Shift+Enter) in cell H4.

 

Cell H5 formula: Uses MMULT Function - Enter formula as an array formula (Ctrl+Shift+Enter) in cell H5 and is shorter than the cell H4 formula.

 

Cell H6 formula: Uses MMULT Function - Hardcode the number of columns in the Range ie. "ROW(1:6)" instead of "COLUMN(A2:F20)" - Normal (non-array) formula in cell H6. Alternatively, "ROW(INDIRECT("1:" &COLUMNS( A2:F20)))" can also be used in cell H6 formula which returns - {1;2;3; 4; 5;6} - instead of using "ROW(1:6)" in the cell H6 formula. 

 

 

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

 

Cell H4 formula Counts the Number of Rows having occurrence of atleast one 1.

 

 

Count Rows with Atleast One 1

    

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

 

 

Cell H4 formula: 

 

 

=SUM(--(FREQUENCY( IF((A2:F20=1), ROW(A2:F20)- ROW(A2)+1), ROW(A2:F20)- ROW(A2)+ 1)>0))  

 

 

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

 

1) IF((A2:F20=1), ROW(A2:F20)- ROW(A2)+1): returns an array of numbers and FALSE values for 6 columns and 19 rows - {FALSE,1,1, FALSE,1, FALSE; FALSE, FALSE, FALSE,2, FALSE, FALSE; FALSE, FALSE, FALSE, FALSE, FALSE, FALSE; 4,4,4, 4,4,4; FALSE, FALSE, FALSE, FALSE, FALSE, 5;...} -  the numbers are the row number for each cell containing 1. For example, the number 1 occurs thrice in the 1st row (A2:F2) which means that the 2nd, 3rd & 5th value is 1 ie. cells B2, C2 & E2 contain 1 and the number 1 indicates the 1st row of A2:F2; the number 2 occurs only once in the 2nd row (A3:F3) in the 4th column which means cell D2 contains 1 and the number 2 indicates the 2nd row of A3:F3; similarly the number 4 occurs in all positions of the 4th row which means that all cells in the range A5:F5 contain 1 and the number 4 indicates the 4th row of A5:F5. Blank cells or cells which do not contain 1, return FALSE. This is the data_array argument of the FREQUENCY function.

 

2) ROW(A2:F20)- ROW(A2)+1: returns an array of values 1 to 19 - {1; 2;3; 4;5; 6;7;8; 9;10;…} - against which the Frequency of each value of data_array is determined ie. this is the bins_array argument of FREQUENCY function. Note that the FREQUENCY function enables calculating the FREQUENCY of a value ONLY ONCE (ie. if a value occurs multiple times, say thrice, then its Frequency is returned as 3 but the value is considered only once. This enables determination of UNIQUE values.   

 

3) FREQUENCY(IF(( A2:F20=1), ROW(A2:F20)- ROW(A2)+1), ROW(A2:F20)- ROW(A2)+1): returns an arry of numbers and zeros for each row - {3;1;0;6; 1;0;1;0; 0;0;0;...} - the numbers are with reference to each row and indicate the number of times the value 1 occurs in a row ex. the number 3 in the 1st position indicates that the number 1 occurs thrice in the 1st row (A2:F2), the next number 1 in the 2nd position indicates that the number 1 occurs once in the 2nd row (A3:F3), and the number 6 in 4th position indicates that the number 1 occurs 6 times in the 4th row (A5:F5) - the position of each number indicates the row number. Equating the array numbers with ">0" return an array of TRUE and FALSE values - {TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE;...} - the rows with a frequency of 1 or more return TRUE, which means that the row has atleast one occurence of 1 (so that a row with 3 or 6 occurrences of 1 will return TRUE only once for that row), and then using the double negation converts TRUE to 1 and FALSE to zero. Using the SUM function returns the total number of rows with atleast one occurrence of 1. 

 

 

Cell H5 Formula: Uses MMULT Function - Enter formula as an array formula (Ctrl+Shift+Enter) in cell H5. 

 

Cell H6 Formula: Uses MMULT Function - Hardcode the number of columns in the Range ie. "ROW(1:6)" instead of "COLUMN(A2:F20)" - Normal (non-array) formula in cell H6. Alternatively, "ROW(INDIRECT("1:"& COLUMNS( A2:F20)))" can also be used in cell H6 formula which returns - {1; 2;3; 4; 5;6} - instead of using "ROW(1:6)" in the cell H6 formula.

  

 

    

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