Let’s say you have a list of mobile nos. and you need to check how many of them are exact 10 digits.
Thus, it will immediately tell you others are incorrect.

Count based on text length

Column A has the list of Mobile nos.

There are 2 ways to count the mobile nos. having 10 digits.

1) Using 1 extra column

  • You can simply create an extra column, Length (B column).
    The formula is simple – in cell B2 write =LEN (A2).
    Then copy the formula down to get the other values.
  • In cell D5, write the formula =COUNTIF(B1:B12,10)
    Countif formula takes 2 things from you – Range and Criteria
    The Range you will ofcourse mention as B1:B12, and the Criteria will be 10.
    It will thus count the no. of 10’s and give you the output.
2) Direct single formula
  • Use an Array formula in cell D8 {=SUM(IF(LEN(A1:A12)=10,1,0))}
  • Let’s understand this formula in parts: –
    • IF(LEN(A1:A12)=10
      It checks each of the cell in the range A1:A12, whether the length is 10 or no.
    • (IF(LEN(A1:A12)=10,1,0)
      After checking, if the length is 10, it gives 1, if not then 0.
      It will now have a 1 for each length = 10.
    • =SUM(IF(LEN(A1:A12)=10,1,0))
      Lastly it will add all the 1’s and 0’s and get you the final number of mobile nos. with length 10 digits.
    • Since this is an array formula, you will have to press Ctrl + Shift + Enter to get the { }.
      Do Not manually type { } in the beginning and end.
Keep Excel-ing with Array formula’s.