Explanation
In this example the goal is to count numbers longer than 15 digits with a formula.
TheCOUNTIF functionmay seem like this logical choice.
you could see this problem in the worksheet below.
All counts in column D areincorrect.
Each number in column B is unique, yet the count returned by COUNTIF suggests the numbers are duplicates.
This problem is related to how Excel handles numbers.
The counting problem mentioned above arises from this limit.
‘999999999999999999) or by formatting the cell(s) as Text before entering.
The solution is to use the SUM function or the SUMPRODUCT function, as explained below.
SUM function
One solution is to replace the COUNTIF formula with a formula that uses theSUM function.
This tells us the number 1234567891234567 occurs just once in the data.
Note: this formula must be entered with Control + Shift + Enter inLegacy Excel.
For more on this topic, seeWhy SUMPRODUCT?
COUNTIF can be used to count…
