Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles COUNT Count numbers third digit equals 3

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:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc