Explanation

In this example, the goal is to count cells in the range B5:B16 that begin with specific text, which is provided in column D. For convenience, the range B5:B16 is named data .

COUNTIF function

The simplest way to solve this problem is with the COUNTIF function and a wildcard. COUNTIF supports three wildcards that can be used in the criteria argument : question mark (?), asterisk(), or tilde (~). A question mark (?) matches any one character and an asterisk () matches zero or more characters of any kind. The tilde (~) is an escape character to match literal wildcards that may appear in data . In this example, we only need to use an asterisk (*).

To count cells in a range that contain “apple” anywhere, you can use a formula like this:

=COUNTIF(range,"*apple*") // contain "apple" anywhere

To count cells in a range that begin with “apple” you can use a formula like this:

=COUNTIF(range,"apple*") // begin with "apple"

In the worksheet shown, we use the criteria in column D directly like this:

=COUNTIF(data,D5)

As the formula is copied down, COUNTIF returns the count of cells in data (B5:B16) that begin with the text seen in D5:D8, which already includes a wildcard. Notice that COUNTIF is not case-sensitive. The text in D5:D8 is all lowercase, yet COUNTIFS happily matches the uppercase text in B5:B16.

SUMPRODUCT function

Another way to solve this problem is to use the SUMPRODUCT function like this:

=SUMPRODUCT(--(LEFT(data,LEN(D5))=D5))
Count cells that begin with SUMPRODUCT and LEFT - 1

Working from the inside out, the LEFT and LEN functions are used to extract the first few characters of each value in data :

LEFT(data,LEN(D5))

Since the text in D5 is “apx”, LEN returns 3, and LEFT returns the first 3 letters of all values in data in an array like this:

{"APX";"APX";"APX";"XKR";"XKR";"XKR";"XKR";"XED";"XED";"XED";"XED";"XED"}

Next, the result from LEFT is compared to the original value in D5. Since Excel is not case-sensitive by default, the result is an array of 12 TRUE and FALSE values like this:

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Notice the TRUE values correspond to cells in data that begin with “apx”.

Next, the double negative (–) converts the TRUE and FALSE values to 1s and 0s, and SUMPRODUCT sums the array and returns the result:

=SUMPRODUCT({1;1;1;0;0;0;0;0;0;0;0;0}) // returns 3

The final result is 3, since there are three values in B5:B16 that begin with “apx”.

Case-sensitive option

Although the SUMPRODUCT formula is more complicated than the COUNTIF formula, the nice thing about using SUMPRODUCT is that we can easily include other functions if needed. For example, to make the formula case-sensitive, we can add the EXACT function . EXACT compares values like this:

=EXACT("apple","apple") // returns TRUE
=EXACT("apple","Apple") // returns FALSE

EXACT only returns TRUE when the two text strings have exactly the same case. We can use EXACT in the formula like this:

=SUMPRODUCT(--EXACT(LEFT(data,LEN(D5)),D5))

This version of the formula uses the LEFT and LEN functions in the same way as the original formula above. The difference is that the EXACT function compares extracted text with the text in column D. Since EXACT is case-sensitive, the entire formula becomes case-sensitive:

Count cells that begin with SUMPRODUCT and EXACT - 2

Notice the value in D6 remains lowercase, and the formula in E6 returns 0 as a result.

SUMPRODUCT with FIND

In Excel, there is always another way to skin the cat :) Here is another case-sensitive formula based on the FIND function:

=SUMPRODUCT(--(IFERROR(FIND(D5,data,1),0)=1))
Count cells that begin with SUMPRODUCT and FIND - 3

This is arguably the most elegant option, since we don’t need the LEN function, the LEFT function, or the EXACT function at all. Because the FIND function is case-sensitive, we can configure FIND to look for the text in column D, and check if the result is 1, since 1 means the text was found starting at the first character.

However, one quirk of the FIND function is that it will return a #VALUE! error if the search text is not found, and this error will bubble up to the top and ruin our formula if we don’t trap it. As a consequence, we have to involve the IFERROR function , just to catch the error and return 0 when it occurs. This makes the formula a bit more cryptic. Still, I do like this formula.

Explanation

In this example, the goal is to count codes that appear as substrings in a case-sensitive way. The functions COUNTIF and COUNTIFS are both good options for counting text values, but these functions are not case-sensitive, so they can’t be used to solve this problem. The solution is to use the FIND function together with the ISNUMBER function to check for substrings and the SUMPRODUCT function to add up the results.

The FIND function is always case-sensitive and takes three arguments: find_text, within_text , and start_num . Find_text is the text we want to look for, and within_text is the text we are looking inside of. Start_num is the position at which to start looking in find_text . Start_num defaults to 1, so we aren’t providing a value in this case, because we always want FIND to start at the first character. When find_text is found inside within_text , FIND returns the position of the found text as a number:

=FIND("ABC","ABC-101") // returns 1
=FIND("ABC","10-ABC-101") // returns 4

When find_text is not found , FIND returns the #VALUE! error:

=FIND("ABC","XYZ-101") // returns #VALUE!

This means we can use the ISNUMBER function to convert the result from FIND into a TRUE and FALSE value. Any number will result in TRUE, and any error will result in FALSE:

=ISNUMBER(FIND("ABC","ABC-101")) // returns TRUE
=ISNUMBER(FIND("XYZ","ABC-101")) // returns FALSE

This idea is explained in more detail here .

In the example shown, we have four substrings in column D and a variety of codes in B5:B15, which is the named range data . We want to count how many times each substring in D5:D8 appears in B5:B15, and this count needs to be case-sensitive.

The formula in E5, copied down, is:

=SUMPRODUCT(--ISNUMBER(FIND(D5,data)))

Working from the inside-out, the FIND function is used to look for a substring like this:

FIND(D5,data)

FIND checks for the value in D5 (“ABC”) in all cells in the data . Because we give FIND multiple values in the within_text argument, it returns multiple results. In total, FIND returns 11 values (one for each code in B5:B15) in an array like this:

{4;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;4;4;#VALUE!;4}

Each number represents a cell in B5:B15 that contains “ABC”. Each #VALUE! represents a value in B5:B15 that does not contain “ABC”. Looking more closely, we can see that FIND found “ABC” in 4 cells out of 11. This array is returned directly to the ISNUMBER function which converts each value to TRUE or FALSE:

ISNUMBER({4;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;4;4;#VALUE!;4})

ISNUMBER returns an array of 11 TRUE and FALSE values:

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}

Because we want to count results, we use a double-negative (–) to convert TRUE and FALSE values into 1’s and 0’s:

--{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}

The resulting array looks like this:

{1;0;0;0;0;0;0;1;1;0;1} // 11 results

Using the double-negative like this is an example of Boolean logic , a technique for handling TRUE and FALSE values like 1’s and 0’s. The resulting array is delivered directly to the SUMPRODUCT function:

=SUMPRODUCT({1;0;0;0;0;0;0;1;1;0;1}) // returns 4

With just one array to process, SUMPRODUCT sums all numbers in the array and returns the final result: 4. As the formula is copied down, it returns a count of each substring in column D. The reference to data does not change, because a named range automatically behaves like an absolute reference .

Note: Because SUMPRODUCT can handle arrays natively, it’s not necessary to use Control+Shift+Enter to enter this formula.