If you’ve used Excel for a while, you realize that working with numbers that have leading zeros can be a pain. Excel treats all formats the same when doing arithmetic calculations, which means that 0023 + 23 still add up to 46. This makes things complicated when you are working with other numbers like product IDs or UPCs that have leading zeros. If 1234 is different from 001234, then you need a way to preserve the zeros. Counting numbers with leading zeros is especially challenging, because the COUNT functions in Excel automatically remove those precious zeros. Here’s a way around the problem, using a little-known ability of **SUMPRODUCT**…

## Examine the Data

Let’s take a look at a sample data set…

We have a series of ProductIDs that have duplicate numbers. Not only that, we also have different ProductIDs that are almost identical – 0199867 and 00199867, for example. The only thing that is different is the leading zero. The numbers are stored as text, so they are currently keeping their leading zeros, and we’ll have to try and keep it that way.

Our goal is to count how many of each ProductID are in the list.

## COUNTIF Attempt

First, let’s try putting together a **COUNTIF** function to do the count. The syntax for **COUNTIF** is as follows:

=COUNTIF(range,criteria)

The *range* will be the column of ProductIDs. We’ll lock the references with **ALT-F4** to make it easier to drag down the formula.

The *criteria* will be what we want to compare. In this case, it’s just the cell in column A of the current row. The final formula in cell **B2** will be:

=COUNTIF($A$2:$A$18,A2)

Drag it down, and let’s look at what we get.

Unfortunately, **COUNTIF** is doing what we predicted… It is stripping the leading zeros from the numbers, even though they are stored as text. For an example, look at the three rows highlighted in orange. Even though there one is 0199867 and the other two are 00199867, **COUNTIF** still counts all three together.

## Counting Numbers with SUMPRODUCT

Fortunately, there is another way… Most people don’t know, but **SUMPRODUCT** can do conditional math, much like **SUMIF** and **COUNTIF** can! The secret is in a wrapper that turns **TRUE** and **FALSE** statements into **1** and **0**, respectively. The way the wrapper works is as follows:

=--(TRUE)

Returns 1.

=--(FALSE)

Returns 0.

When we combine it with a **SUMPRODUCT** function, we can add up only the **TRUE** statements!

**SUMPRODUCT** normally takes multiple arrays (you can brush up on **SUMPRODUCT** syntax here), but we can build our own array by adding a conditional test. In this case, we want to compare column **A** to the item in the current row. Wrap the conditional in the **–()** wrapper, and the final **SUMPRODUCT** formula for cell **C2** is as follows:

=SUMPRODUCT(--($A$2:$A$18=A2))

Drag down the formula and let’s look at the result.

And that’s it! That’s what we want! Leading zeros are intact. 0199867 has one entry, and 00199867 has 2. The other entries behave the same way.

This is what I am looking for. Thank you !