User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active


Count Number of Rows with occurrence of atleast one 1



Count Number of Rows in a Multi Column-Row Array, having 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: Counts the Number of Rows with occurrence of atleast one 1. Uses Frequency Function - Enter formula as an Array formula (Ctrl+Shift+Enter) in cell H4.




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


Count Rows with Atleast One 1 


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. 





Related Formulas - illustrated in the Ebook of Excel Formulas.  


Cell H5 Formula: Uses MMULT Function and is shorter compared to cell H4 formula - 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.