User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

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

 

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

 

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.

 

 

-----------------------------------------------------------------------------------------------

 

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

       

"Open Image in New Tab" for a full and clear view. 

 

 

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.

 

 

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.

 

  

    

Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.