Count the number of Cells containing Specific Letters / Characters
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 the number of Cells containing Specific Letter(s) / Character(s), as part of a String
Column G formulas Count the number of Cells in column A which contain Specific Letter(s). The Specific Letters or Text to be considered in each cell is case-insensitive viz. it can be E or e, L or l, and so on. Note: Formulas use CAPITAL letters with the SUBSTITUTE function (which is case-sensitive) – hence UPPER function is used which converts each string to uppercase.
Cell G6 formula: Counts Number of cells in column A containing 2 “E” & 1 “L”: each cell which contains exactly two E’s AND one L (not less and not more) is counted.
———————————————————————————————–
Cell G6 formula Counts Number of cells in column A containing 2 “E” & 1 “L”: each cell which contains exactly two E’s AND one L (not less and not more) is counted. The Specific Letters or Text to be considered in each cell is case-insensitive viz. it can be E or e, L or l, and so on.
Count Cells with Specific Letters
Cell G6 formula:
=SUMPRODUCT((LEN( A2:A20)- LEN( SUBSTITUTE( UPPER( A2:A20), “E”, “”))=2)*( LEN( A2:A20)- LEN( SUBSTITUTE( UPPER(A2:A20), “L”, “”))=1))
Explanation – Cell G6 formula (Non-Array Formula):
1) LEN(SUBSTITUTE( UPPER( A2:A20), “E”,””)): returns array of values – {6;9; 5;2; 8;7; 11; 5;7; 9;9; 10; 0;0; 0;0;0; 0;0} – the numbers represent the string length in each cell without “E”. For example, the 1st number 6 indicates that cell A2 string has a total of 6 characters, after deleting the 2 E’s (9 characters of cell A2 string minus 2 E’s). Note: using the UPPER function converts all string letters into upper case and then using capital “E” with the SUBSTITUTE function (which is case-sensitive) removes all E’s in a string whether in upper or lower case.
2) LEN(A2:A20)- LEN( SUBSTITUTE( UPPER( A2:A20), “E”,””)): returns an array of numbers – {2;2; 1;1; 2;1;1; 1;2;4; 2;1;0; 0;0;0; 0;0;0} – the numbers are the difference in each string length before and after removing E’s in each string ie. deducting the length of each string without E from the original string length. This calculates the number of E’s in each cell string. Equating this with “=2” returns an array of TRUE / FALSE values, where TRUE indicates the cells which have exactly 2 E’s.
3) (LEN(A2:A20)- LEN( SUBSTITUTE( UPPER( A2:A20), “L”,””))=1): this returns an array of TRUE and FALSE values where TRUE is returned for each string which has exactly one “L”. Similar logic is used as in 2) above which determines exactly 2 “E”.
4) Using SUMPRODUCT will multiply both the above arrays of 2) and 3) above, and return 1 where both conditions (of 2 E’s & 1 L) are satisfied – {1;1; 0;0; 1;0; 0;0; 1;0; 1;0; 0;0; 0;0; 0;0;0} – and then adds these 1’s which returns the Count of the number of cells satisfying the 2 conditions.
—————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Cell G7 Formula: Counts Number of cells in column A containing 2 “E” & 2 “O”: each cell which contains exactly two E’s AND two O’s (not less and not more) is counted. Non-array formula.
Cell G8 Formula: Counts Number of cells containing 2 “RE” & 2 “L”: each cell with exactly two RE’s AND two L’s (not less & not more) is counted – it should be RE consecutively (Note: RAE or ER is not counted). Non-array formula.
Cell G9 Formula: Counts Number of cells containing 1 or 2 “E” & 1 or 2 “L”: a cell should contain “EITHER One OR Two E’s” AND “EITHER One OR Two L’s” – if a cell contains 2 E’s and 3 L’s it is not counted. Non-array formula.