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.

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.