You can use the LEN and SUMPRODUCT functions to count the number of cells with a certain length and a text string.
Example: You are working with a worksheet with the first name in column A, the last name in column B, and the region in column C.
Question: How many people's last names have eight characters and "Williams"?
=SUMPRODUCT(–(LEN(B2:B12)=8), –ISNUMBER(SEARCH("WILLIAMS", B2:B12)))
=SUMPRODUCT((LEN(B2:B12)=8)*1, ISNUMBER(SEARCH("WILLIAMS", B2:B12))*1)
The result returns 3, so three people have last names with eight characters and "Williams".
In column B, there are six last names with eight characters, and three are "Williams". Please note that the SEARCH function is not case-sensitive, so both "WILLIAMS" and "Williams" are counted.
Explanation:
- Step 1: LEN(B2:B12): To count the length of each cell;
- Step 2: (LEN(B2:B12)=8): If the length equals 8, the result returns TRUE; otherwise, it returns FALSE;
- Step 3: –(LEN(B2:B12)=8): The double hyphen (or times 1) converts TRUE into one and FALSE into zero;
- Step 4: SEARCH("WILLIAMS", B2:B12): To search the text location. The result returns a number if the cell contains the text; otherwise, it returns FALSE;
- Step 5: ISNUMBER(SEARCH("WILLIAMS", B2:B12)): The result returns TRUE if the cell contains the text, or FALSE if not;
- Step 6: –ISNUMBER(SEARCH("WILLIAMS", B2:B12)): The double hyphen (or times 1) converts TRUE into one and FALSE into zero;
- Step 7: The SUMPRODUCT function returns the sum of the products of the new array.
To count case-sensitive text, you can change the SEARCH function with the FIND function. For example, how many people have last names with eight characters and "WILLIAMS" (upper case)?
=SUMPRODUCT(–(LEN(B2:B12)=8), –ISNUMBER(FIND("WILLIAMS", B2:B12)))
=SUMPRODUCT((LEN(B2:B12)=8)*1, ISNUMBER(FIND("WILLIAMS", B2:B12))*1)
The following are the details from step 1 to step 6:
| Last Name | Step 1 | Step 2 | Step 3 | Step 4 | Step 5 | Step 6 |
|---|---|---|---|---|---|---|
| JOHNSON | 7 | FALSE | 0 | #VALUE! | FALSE | 0 |
| Jones | 5 | FALSE | 0 | #VALUE! | FALSE | 0 |
| BROWN | 8 | TRUE | 1 | 1 | TRUE | 1 |
| JONES | 5 | FALSE | 0 | #VALUE! | FALSE | 0 |
| MARTINEZ | 8 | TRUE | 1 | #VALUE! | FALSE | 0 |
| BROWN | 4 | FALSE | 0 | #VALUE! | FALSE | 0 |
| WILLIAMS | 8 | TRUE | 1 | 1 | TRUE | 1 |
| ANDERSON | 8 | TRUE | 1 | #VALUE! | FALSE | 0 |
| WILLIAMS | 8 | TRUE | 1 | 1 | TRUE | 1 |
| Johnson | 8 | TRUE | 1 | #VALUE! | FALSE | 0 |
| SMITHs | 7 | FALSE | 0 | #VALUE! | FALSE | 0 |