Explanation
In this example, the goal is to count numbers in the range B5:B15 ( named data ) where the third digit is a specific number, indicated in column D. You might think the COUNTIF function would be a good way to solve this problem. However, for reasons explained below, COUNTIF won’t work. Instead, you can use the SUMPRODUCT and Boolean logic . See below for a full explanation.
COUNTIF function
The COUNTIF function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (,?) for partial matching. You would think you could use the COUNTIF function with the question mark (?) and asterisk () wildcards to count numbers where the third digit is 1 like this:
=COUNTIF(data,"??1*") // returns 0
However, COUNTIF will return zero. The problem is that using any wildcard in criteria means that COUNTIF will interpret the pattern as a text value, whereas the values in column B are numeric . As a result, COUNTIF will never find a matching number and the result will always be zero. As a workaround, you might try the trick below to coerce the numbers in data to text by concatenating an empty string ("") to the range like this:
=COUNTIF(data&"","???1") // throws error
However, this will cause Excel to throw the generic “There’s a problem with this formula” error, so it’s not possible to even enter the formula. This happens because COUNTIF is in a group of eight functions that require an actual range for range arguments. This means you can’t use an array operation to modify the range argument inside COUNTIF, COUNTIFS, SUMIF, SUMIFS, etc.
SUMPRODUCT function
Another way to solve this problem is with the SUMPRODUCT function , the MID function , and Boolean logic . To count numbers in data where the third digit is 1, we can use SUMPRODUCT like this:
=SUMPRODUCT(--(MID(data,3,1)="1")) // returns 2
Working from the inside out, the MID function is used to extract and test the third digit from the numbers in data like this:
MID(data,3,1)="1"
Because we give MID 11 numbers in the range B5:B15, MID returns an array with 11 results:
{"3";"4";"5";"2";"3";"2";"4";"1";"3";"3";"1"}="25"
Note that MID automatically converts the numbers to text, so we use the text value “1” for the comparison. The result is an array of TRUE and FALSE values:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}
In this array, TRUE values correspond to numbers where the third digit is 3. We want to count these results, but first we need to convert the TRUE and FALSE values to 1s and 0s. To do this, we use a double negative (–).
--{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}
The resulting array contains only 1s and 0s and is delivered directly to the SUMPRODUCT function:
=SUMPRODUCT({0;0;0;0;0;0;0;1;0;0;1})
With only a single array to process, SUMPRODUCT sums the items in the array and returns 2 as result. In this formula, note we are hardcoding the value “1” and we set num_chars to 1 inside the MID function. To adapt the formula for the worksheet shown, we a reference to cell D5 like this:
=SUMPRODUCT(--(MID(data,3,1)=D5))
As the formula is copied down, it returns the count of numbers in data where the third digit equals the numbers in column D. Note that the numbers in column D are entered as text values , since the result from MID will also be text. You can avoid this requirement by adapting the formula to coerce the value in D5 to text:
=SUMPRODUCT(--(MID(data,3,1)=D5&""))
Here we concatenate an empty string ("") to the D5, which will convert a numeric value to text. This version of the formula will work when there are numbers in column D, or text values.
Note: In Excel 365 and Excel 2021 you can use the SUM function instead of SUMPRODUCT if you prefer. This article provides more detail .
Explanation
In this example, the goal is to count ages in column C according to the brackets defined in columns E and F. All data is in an Excel Table named data defined in the range B5:C16. A simple way to solve this problem is with the COUNTIFS function. If you are using Excel 365 or Excel 2021, another easy way to solve this problem is with the FREQUENCY function. Both approaches are explained below.
COUNTIFS function
The COUNTIFS function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Conditions are supplied to COUNTIFS in the form of range/criteria pairs — each pair contains one range and the associated criteria for that range:
=COUNTIFS(range1,criteria1,range2,criteria2,etc)
Using this pattern we can count ages in brackets like this:
=COUNTIFS(data[Age],">=20",data[Age],"<=29") // 20-29
=COUNTIFS(data[Age],">=30",data[Age],"<=39") // 30-39
=COUNTIFS(data[Age],">=40",data[Age],"<=49") // 40-49
We are using the structured reference data[Age] for both ranges, since data is an Excel Table . Using an Excel Table means the table will expand automatically as new rows are added, and the counts will remain up to date.
Notice in the formulas above, we are hardcoding the numbers into the formula. This is generally considered a bad practice, since it’s easy to make a mistake when entering the formula and it’s more difficult to adjust the ranges later if needed, especially since each age bracket has its own unique formula. A better approach is to use ages already on the worksheet in columns E and F.
To do this, we need to concatenate the ages in columns E and F to the logical operators >= and <=. The final formula in G5 looks like this:
=COUNTIFS(data[Age],">="&E5,data[Age],"<="&F5)
Notice the operators are enclosed in double quotes ("") and attached to cell references E5 and F5 with an ampersand character (&). For a detailed overview of concatenation, see How to concatenate in Excel . As this formula is copied down, the reference to data[Age] behaves like an absolute reference and does not change, which means we can use the same formula for all five age brackets.
If you need a final bracket that captures all ages above 70, you can use a single condition like this:
=COUNTIFS(data[Age],">=70") // 70+
This formula will return a count of all ages greater than or equal to 70.
FREQUENCY function
If you are using Excel 365 or Excel 2021, where array formulas are native , a nice way to solve this problem is with the FREQUENCY function . The FREQUENCY function returns more than one value at a time and needs to be entered as a multi-cell array formula in Legacy Excel . In modern Excel, you can simply use a formula like this in cell G5:
FREQUENCY(data[Age],F5:F9)
Here, the data_array is given as data[Age] and the bins_array is given as F5:F9, the “End” values in column F. The FREQUENCY function performs the calculation and returns an array like this:
{3;3;1;3;2;0}
Each number represents a count, and the results automatically spill into multiple cells. Notice that by design, the FREQUENCY function always returns a count for one more bin than is provided. This is called the overflow bin, and represents the count of any values greater than the largest value in the bins_array . To suppress this last count, you can nest the FREQUENCY function inside the DROP function like this:
=DROP(FREQUENCY(data[Age],F5:F9),-1)
The DROP function will remove the last row in the array returned by FREQUENCY and the result will match the values returned by COUNTIFS above.
With a Pivot Table
A Pivot Table is another way to solve this problem. See this video for a similar example .
