Excel is able to count only 15 significant numbers and hence cannot compute numbers that are longer than 15 digits. Excel will give an erroneous result if it tries to compute long digits with COUNTIF. COUNTIF function only works perfectly for digits less than 16 but not the other way around.
Once the number exceeds 15 digits, excel changes the rest of the numbers to zero, making it impossible to get valid results from any computation applied to it.
Some ways to maneuver this error tendency might be to enter the digits as text i.e. by inserting a single quote just before the digits in a cell e.g ‘12345678911235678 or by changing the format of the cell to text before entering the number.
While these methods are good, they are only useful if no computation is to be applied on the numbers i.e. plain long numbers like credit card numbers. However, if the long numbers are for computational purposes, then the formula below should be used.
SUMPRODUCT(--(A:A=A1)) also used as
How to Count Long Numbers Without COUNTIF
The formula for counting long numbers without COUNTIF is easy and will be clearer as soon as the parameters are explained.
- SUMPRODUCT: This is the function that does the computation. It could also be replaced by the SUM function.
- Double negation (–): The double negation coerces the TRUE/FALSE result of the SUMPRODUCT computation to 1/0.
- A:A: This refers to the named range i.e. the column containing the data to be computed e.g. B3:B7.
- A1: This refers to each cell where the count should be computed.
If you want to enter long digits in a cell, Excel adds a trail of zeros and makes it difficult to use them in computation.
The example shows the erroneous way excel handles digits above 15.
- How Excel represents a 25-digit number
Figure 1 – Showing the 25-digit number in standard form
- How excel alters the validity of the number by adding a trail of zeros
Figure 2 – Excel adds a trail of Zeros to the number after 15 digits
Let us assume that you have a list of long numbers and you need to count how many digits each number is, the following steps should be taken.
- Write the data to be counted
Figure 3 – Showing the data list
- Enter the SUMPRODUCT formula
Figure 4 – Showing the list of students with their names and date of birth
- Showing the count value
Figure 5 – Showing the data-count table
- Apart from COUNTIF, some other functions that have the same issues are SUMIF, SUMIFS, COUNTIFS, AVERAGEIF, AVERAGEIFS.
- If any of the above formulas are used for computation purposes, the results will be unreliable.
- Apart from using SUMPRODUCT function, you can also use the SUM function
- The SUM function only works when CONTROL+SHIFT+ENTER because it is an array formula.
- The SUM function is called the Array formula variant and should be written in between to curly braces.
- When using the Array formula variant, control + shift + enter should not be used.
- It is important to note that the part of the formula – A:A– must be replaced by a named range e.g. B6:B10 as seen in the example above.
If not, the result will not be computed.