Have you ever faced an Excel situation wherein you need to count or calculate rows and columns of data uniquely? Or in other way of saying — to count duplicate data only once? This can done using “Pivot Table” approach, but Excel also has several formulas that you can exploit to come up with the same results.
Suppose you are operating an online store. Your website’s back-end e-commerce system reports sales per item and per day, like the table below:
Suppose further that you want to know how many unique lists are sold per day. In this sample scenario, you cannot easily count the number of lists sold per day because you also need to ensure that the lists are unique. This means that using the data above, only one (1) product was sold on 8/3/2013, up to 8/6/2013.
Here is the completed table:
The “Unique Count” column is the main solution of the scenario. Excel shall check if the products sold are duplicates. If they are duplicates, it shall count the first instance of the product in the list and the duplicates rows shall be blank. Also, the date is counted in similar pattern. In a particular date, Excel shall count all unique products — counting duplicates only once.
In order to calculate the Unique Count value, this formula is used:
=IF(SUMPRODUCT(($B$5:$B5=B5)*($C$5:$C5=C5))>1,"",1)
The SUMPRODUCT Function
In MS Excel, the SUMPRODUCT function multiplies corresponding components in the given arrays, and returns the sum of those products. It has the following syntax:
SUMPRODUCT(array1, [array2], [array3], ...)
The SUMPRODUCT function shown above has the following arguments:
- Array1 Required. The first array argument whose components you want to multiply and then add.
- Array2, array3,… Optional. Array arguments 2 to 255 whose components you want to multiply and then add.
The Unique Count Formula
In the Unique Count formula, =IF(SUMPRODUCT(($B$5:$B5=B5)*($C$5:$C5=C5))>1,””,1), the first array is the column B and the second is column C. If data are equal, or duplicates with other columns, Excel will return blank. Otherwise, it shows the count “1”.
You can download the actual sample file in the link below.