As the title suggests that we need to count the numbers wherever the third digit of stepping of numbers is 3. can be done by combining the Excel built-in functions, the SUMPRODUCT function, and the mid function.
Generic Formula
=SUMPRODUCT(--(MID(count_range, 3,1)=”3”))
How does this formula work?
- Firstly, the innermost function is the Excel MID function which includes the parameters the first generic parameter count-range represents the range to be included for the count. The 2nd parameter represents the starting number and the 3rd parameter represents the total number of characters.
- Secondly, the result from MID function is : {“3”; ”3”; ”1”; ”7”}. The function returns the third character as a text value.
- Thirdly, we compare the result of the mid function with an array=”3” and therefore we obtain an array of logical TRUE or FALSE values which looks something like{TRUE; TRUE; FALSE; FALSE}where TRUE returned for every match with the array ”3”.
- Fourthly, now theses array ranges obtained need to be converted to logical ‘1’ represents the logical TRUE and 0 represents the logical FALSE and a is obtained which looks like {1;1;0;0}.
- Finally, the Excel SUMPRODUCT function returns the sum of the items in a range of items
Example
Let us consider a random array of numbers containing 5 digits in and try to find out how many numbers contain their this digit as 3.
Figure 1. The SUMPRODUCT and MIN functions applied as a part of the generic formula
The result obtained when used the generic formula is as shown below.
Figure 2. The result obtained when applied the generic formula
Leave a Comment