where data is the named range B5:B15. Note: The COUNTIF function will not count the numbers in this example correctly, as explained below.
This problem is related to how Excel handles numbers. Excel can only handle 15 significant digits, and if you enter a number with more than 15 digits in Excel, you will see the trailing digits silently converted to zero. The counting problem mentioned above arises from this limit. Normally, you can avoid this limit by entering long numbers as text, either by starting the number with a single quote (i.e. ‘999999999999999999) or by formatting the cell(s) as Text before entering. As long as you don’t need to perform math operations on a number, this is a good solution, and it will handle long numbers like credit card numbers and serial numbers without losing any numbers. However, if you try to use COUNTIF to count a number with more than 15 digits (even when stored as text) you may see incorrect results. This happens because COUNTIF internally converts the text value back to a number at some point during processing, which triggers the 15 digit limit described above. With trailing digits converted to zero, long numbers may look like duplicates to RACON functions like COUNTIF, COUNTIFS, SUMIF, SUMIFS, etc. 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 the SUM function. In the example shown, the formula in E5 is: The formula uses the named range data (B5:B9) and Boolean logic to count values. Working from the inside out, this expression compares all values in data (B5:B15) with the value in B5: Because data contains 11 cells, the result is an array of TRUE/FALSE results like this: Notice that the TRUE value corresponds to the first number in the range B5:B15, and all remaining values are FALSE. This tells us the number 1234567891234567 occurs just once in the data. Because the SUM function will ignore the logical values TRUE and FALSE, we use a double negative (–) to convert the TRUE and FALSE values to 1s and 0s: This results in an array containing only 1s and 0s, which is returned directly to the SUM function: The SUM function sums the items in the array and returns 1 as a final result. Note: this formula must be entered with Control + Shift + Enter in Legacy Excel.
SUMPRODUCT function
In Legacy Excel, the formula above is an array formula and must be entered in a special way. To avoid this step, you can replace the SUM function with the SUMPRODUCT function like this: The behavior of the formula is the same, but because SUMPRODUCT is in a small group of functions that can handle array operations natively, the formula requires no special handling. For more on this topic, see Why SUMPRODUCT?
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
