Go Back

Count numbers third digit equals 3

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?

  1. 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.
  2. Secondly, the result from MID function is : {“3”; ”3”; ”1”; ”7”}.  The function returns the third character as a text value.
  3. 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”.
  4. 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}.
  5. 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

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I am trying to assign a 10 digit number in a column that has the 1st 3 digits of the number based on a number in another column. The 10 digits numbers can not be duplicate and need to be sequenced as such. If Column F4 equals 414, then Column H4 equals 414000001. If column F5 equals 430, then Column H5 equals 430000001. If column F6 equals 414, then Column H6 equals 414000002.
Solved by O. S. in 11 mins
Hello, I am trying to enter formula into excel to match below: > 95% equals 5 90% - 94.9% equals 4 85% - 84.9% equals 3 75 - 85% equals 2 <75% equals 1 Could you pleased help me? Thanks
Solved by A. S. in 16 mins
I have certain number of range from which i need to know last digit or second last digit or third last digit of the range available
Solved by M. Q. in 24 mins

Leave a Comment

avatar