Count cells where a value appears atleast once (in whole or as part of a string), in Multiple Non-Contiguous Ranges
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 cells where a value appears atleast once (OR Count All Occurrences of the Value) – either individually or as part of a string, in Multiple Non-Contiguous Ranges
Cell E5 formula: Count Cells in Multiple Non-Contiguous Ranges, where a value occurs atleast once in whole or in part.
———————————————————————————————–
Count Non-Contiguous Ranges with Value
Count in Non-Contiguous Ranges
Cell E5 formula Counts cells with occurrence of cell D5 value (“ryan”), individually or as part of string in a cell (a cell containing “Ryan” or “Bryan” or “ryan ryan” is counted only once). Case-insensitive search, so that both “Ryan” or “ryan” are counted.
Count in the 4 ranges: A2:A5, A8:A11, A14:A17 and A20:A23. Ranges are non-contiguous: every 4 rows and then a gap of 2 rows each.
Cell E5 formula:
=SUM(–ISNUMBER( SEARCH( D5, IF(MOD( ROW( A2:C23)- ROW(A2), ROWS(A2:A7)) <=3, A2:C23))))
Explanation – Cell E5 formula (Array Formula – Ctrl+Shift+Enter):
1) IF(MOD( ROW( A2:C23)- ROW(A2), ROWS(A2:A7)) <=3, A2:C23): returns the ranges – A2:A5, A8:A11, A14:A17 and A20:A23 – in the overall range of A2:C23. Using MOD, with the divisor of 6 ie. ROWS(A2:A7) – “MOD(ROW(A2:C23)-ROW(A2),ROWS(A2:A7))” – returns an array of numbers – {0;1;2; 3;4;5; 0;1;2; 3;4;5; 0;1; 2;3; 4;5; 0;1; 2;3} – with 0 to 3 representing the 1st 4 rows in each group of 6 rows, so using “<=3” will consider the 1st 4 rows, then skip 2 rows, then consider the next 4 rows, then skip 2 rows, and so on.
2) Using the SEARCH function to look for D5, will return the position of occurrence of cell D5 value in whole or in part in each cell of the range(s) as considered in 1) above.
3) –ISNUMBER( SEARCH(D5, IF(MOD( ROW(A2:C23)- ROW(A2), ROWS(A2:A7))<=3, A2:C23))): returns an array of 1s and zeros, for 3 columns in each row of the range – {1,0,0; 0,1,0; 0,0,0; 0,1,0; 0,0,0; 0,0,0; 0,0,0; 1,0,0; 0,0,1; 1,0,0; 0,0,0; 0,0,0; 0,0,0; 1,0,0; 0,0,0; 1,0,0; 0,0,0; 0,0,0; 0,0,0; 1,0,1; 0,1,0; 0,0,0} – the 1s indicate the cells which contain cell D5 value in whole or in part. Using SEARCH function returns the position of 1st occurrence of cell D5 value in each cell – {1,#VALUE!, #VALUE!; #VALUE!,2, #VALUE!; #VALUE!, #VALUE!,#VALUE!; #VALUE!,7, #VALUE!;…} – and using ISNUMBER returns TRUE for these positions and FALSE for error values in cells not containing cell D5 value, and using double negation converts TRUE to 1 and FALSE to zero. For example, the 1st number in 1st row / 1st column indicates that cell A2 contains cell D5 value starting from the 1st position, the next number 2 in 2nd row / 2nd column indicates that cell B3 contains cell D5 value starting from the 2nd position, and so on. Note that TRUE or FALSE is returned only once for a cell containing or not containing cell D5 value, so that the number 1 will be returned for each cell where D5 occurs in whole or part. Using SUM will return the number of cells with atleast 1 occurrence of cell D5 value in whole or in part.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Other formula(s):
Count All Occurrences of a Value, appearing either individually or as part of a string, in Multiple Non-Contiguous Ranges
Cell E6 formula: Count ALL occurrences of cell D5 value (“ryan”), individually or as part of string in a cell (a cell containing “ryan ryan” will be counted twice). Count in the 4 ranges: A2:A5, A8:A11, A14:A17 and A20:A23. Ranges are non-contiguous: every 4 rows and then a gap of 2 rows each. Case-insensitive search, so that both “Ryan” or “ryan” are counted. Array formula (Ctrl+ Shift+ Enter).