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