Excel Formulas - Count long number of more than 15 digits not using COUNTIF()

Count long number of more than 15 digits not using COUNTIF()

Syntax of used function(s)

SUMPRODUCT(array1, [array2], [array3], ...)

The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products.

Explanation

Excel can only handle 15 significant digits, and if a number with more than 15 digits in Excel, the trailing digits silently converted to zero. So, in case of more than 15 ditits number a counting problem arises in excel. It can be avoided by entering long numbers as text, either by starting the number with a single quote or by formatting the cell(s) as Text.
The COUNTIF function provide an unreliable result to count a number with more than 15 digits, because, the COUNTIF function internally converts the long value back to a number. That is why the SUMPRODUCT function can solve the problem.

Formula

=SUMPRODUCT(--(B6:B10=B6))

How this formula works

In the above formula the SUMPRODUCT compares all values in the range B5:B10 with the value of column B in the current row. This results in an array of TRUE or FALSE results. Now the formula looks like -

=SUMPRODUCT(--({TRUE;FALSE;FALSE;FALSE;FALSE;}))

Therefore the -- signs converts the TRUE or FALSE into 1 or 0, and the formula looks like -

=SUMPRODUCT(--({1;0;0;0;0;}))

Therefore the SUMPRODUCT simply sums the items in the array and returns the result.

Previous: Count Formulas - Count paired items in a row column combination from a guide
Next: Excel Formulas - Count cells which are not equal either one or another cirteria