Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Count numbers third digit equals 3 in Excel

This tutorial shows how to Count numbers third digit equals 3 in Excel using the example below;




To count numbers where the third digit equals 3, you can use a formula based on the SUMPRODUCT and MID functions. In the example shown, the formula in E5 is:


How this formula works

To get the third character from a string in A1, you can use the MID function like this:


The first argument is a cell reference, the second argument specifies the start number, and the third argument indicates number of characters.

Also See:   Highlight duplicate rows in Excel

If you give the MID function a range of cells for the first argument, you’ll get back an array of results. In the example shown, this expression:


returns an array like this:


This array contains the third digit from each cell in the range B5:B12. Notice the MID function has automatically converted numeric values in the range to text strings and returned the third character as a text value.

Also See:   Count cells equal to case sensitive in Excel

When we compare this array using =”3″, we get an array like this:


We use the double negative to coerce the TRUE and FALSE values to 1 and zero respectively, which returns:


Finally, with only one array to work with, the SUMPRODUCT function sums the items in the array and returns the total, 3.

Also See:   Convert text timestamp into time in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *