< 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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar