# 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

**Weekly Trends and Language Statistics**- Weekly Trends and Language Statistics