Count Number of Rows in a Range where Multiple Values appear atleast once in the Same Row
Count Number of Rows, wherein Multiple Specified Values appear in a Row
Cell K3 formula Counts Number of Rows in a Range where Multiple Values appear atleast once in the Same Row.
Cell K3: Number of rows in range (A2:E10), where G1:H1 values (“pear” & “melon”) appear in same row.
Note: Cell K3 formula will also match a Blank cell, if any, in cells G1 / H1.
———————————————————————————————–
Cell K3 formula Counts Number of rows in range (A2:E10), where G1:H1 values (“pear” & “melon”) appear atleast once in the same row.
Cell K3 formula:
This formula checks specifically 2 values of G1 & H1 to appear in the same row.
=SUM((FREQUENCY( IF( A2:E10=G1, ROW(A2:E10)), ROW(A2:E10))>0)*( FREQUENCY( IF( A2:E10=H1, ROW(A2:E10)), ROW( A2:E10))>0))
Explanation – Cell K3 formula (Array Formula – Ctrl+Shift+ Enter):
1) IF(A2:E10=G1, ROW(A2:E10)): returns an array of numbers and FALSE values covering 5 columns and 9 rows of the range “A2:E10” – {FALSE,2, FALSE,2,2; FALSE, FALSE, 3,FALSE,3; FALSE, FALSE, FALSE, FALSE, FALSE;5,FALSE, FALSE, FALSE, FALSE;…} – the numbers represent the row number where cells match cell G1 value, for example the number 2 indicates that the 2nd row contains “pear” (cell G1) in cells B2, D2 & E2 and these three cells return 2 while the cells A2 & C2 return FALSE as in “FALSE,2, FALSE, 2,2”. The next number 3 indicates that the 3rd row contains “pear” (cell G1) in cells C3 & E3 and these two cells return 3 while the cells A3, B3 & D3 return FALSE as in “FALSE, FALSE, 3, FALSE,3”. This is the data_array argument of the FREQUENCY function.
2) ROW(A2:E10): returns an array of row numbers from 2 to 10 – {2;3; 4;5; 6;7;8; 9;10} – against which the Frequency of each value in 1) above 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 twice, then its Frequency is returned as 2 but the value is considered only once). This enables determination of UNIQUE values.
3) FREQUENCY( IF(A2:E10=G1, ROW(A2:E10)), ROW(A2:E10)): returns an array of numbers – {3;2; 0;1; 1;2;1; 0; 0;0} – the numbers represent the number of times the cell G1 value occurs in each row. For example, the 1st number 3 means that the number 2 occurs thrice in the array of 1) above ie. the cell G1 value of “pear” appears thrice in the row number 2 ie. A2:E2. The next number 2 means that the number 3 occurs twice in the array of 1) above ie. the cell G1 value of “pear” appears twice in the row number 3 ie. A3:E3. Checking this with “>0” will return an array of TRUE and FALSE values – {TRUE;TRUE; FALSE; TRUE; TRUE;TRUE; TRUE; FALSE; FALSE; FALSE} – where TRUE indicates that cell G1 value appears atleast once in the corresponding row. So, the 1st TRUE, in 1st position, indicates that cell G1 appears atleast once in the 1st row (A2:E2). In a mathematical operation TRUE converts to 1 and FALSE converts to zero.
4) (FREQUENCY( IF(A2:E10=H1, ROW(A2:E10)), ROW(A2:E10))>0): similar to above, this returns an array of TRUE and FALSE values – {FALSE;TRUE; FALSE; TRUE; TRUE; FALSE; TRUE;FALSE; FALSE; FALSE} – where TRUE indicates that cell H1 value appears atleast once in the corresponding row. So, the 1st TRUE, in the 2nd position, indicates that cell H1 appears atleast once in the 2nd row (A3:E3).
5) Multiplying the 2 FREQUENCY functions ie. 3) & 4) above, both will return an array of 1s and zeros – {0;1;0;1; 1;0; 1;0; 0;0} – where the 1s indicate that both cell G1 and H1 values appear in the same row. The 1st 1, in 2nd position, indicates that BOTH cell G1 & H1 values occur atleast once in the 2nd row (A3:E3). Using SUM adds the 1s to return the number of rows in which both G1 & H1 values appear atleast once.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Cell K4 Formula: Counts Number of rows in range (A2:E10), where G1:I1 values “pear”, “melon” & “mango” appear in same row – Array formula (Ctrl+ Shift+ Enter).
Cell K5 Formula: Counts Number of rows in range (A2:E10), where G1:J1 values “pear”, “melon”, “mango” & “peach” appear in same row – Array formula (Ctrl+Shift+Enter). While cell K3 / K4 formulas do a separate calculation for each cell value (ie. G1, H1 & I1) making the formula unwieldy for large number of cells, whereas the cell K5 formula can Match multiple cell values at one time without repeating a separate calculation for each cell Match – just update range “G1:J1” in the formula to include more cells.
Note: Cell K3 / K4 formulas will also match a Blank cell, if any, in cells G1 / H1 / I1. Cell K5 formula considers and matches only non-blank cells in the range G1:J1.