This array contains the third digit from each cell in the range B5:B12. Notice the MID function has automatically converted numeric values in the range to text strings and returned the third character as a text value.
When we compare this array using ="3", we get an array like this:
We use the double negative to coerce the TRUE and FALSE values to 1 and zero respectively, which returns:
Finally, with only one array to work with, the SUMPRODUCT function sums the items in the array and returns the total, 3.
COUNTIF counts the number of cells in the range that contain five characters by matching the content of each cell against the pattern "?????", which is supplied as the criteria for COUNTIF. The "?" symbol is a wildcard in Excel that means "match any...
This formula works by using SUBSTITUTE to first remove all of the characters being counted in the source text. Then the length of the text (with the character removed) is subtracted from the length of the original text. The result is the number of...
COUNTIF counts the number of cells in the range that begin with txt by matching the content of each cell against the pattern "txt*", which is supplied as the criteria. The "*" symbol (the asterisk) is a wildcard in Excel that means "match any number...
The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl".
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.