Sometimes, we want to calculate the number of unique values in columns, and then
prepare a summary but cannot do using a single formula or without changing the
sheet structure. One way is to remove the number of duplicates and check the
count and other way is pivot table, but in both these methods, a summary report
involving multiple columns can’t be made, since pivot table will count the number
of non blank cells, rather than unique cells. One method to do this is using
Countif formula, shown below by an example.
Suppose, we are having sales data where different product codes have been sold by
3 salesmen from old and new stock and we want to find out how much unique products
have been sold and how many unique products have been sold from old and new stock
and prepare a summary for these.
Now, we want to find out unique products sold and we need to count unique product
codes sold.
For this, we will insert a column D and write the formula in cells D2
=IF(COUNTIF(C$2:C2,C2)>1,0,1)
As seen in the formula, the formula will count the number of occurrences and as the
range increases and repetition occurs, the values becomes greater than 1, and
if statement gives 0, thereby excluding the repetition.
Drag the formula till the end and then filter out ‘1’ on column D, we will find the
unique products sold in the month. There are 12 unique products
Now, the second problem faced is counting the unique products sold from old and new
stock. This means that there will be some products sold from both old as well
as new stock.
Now, we will have to create a unique key where we concatenate columns C & E to
create a key and then count their unique occurrences. Formula used is = C2&E2
in column F.
Now, to calculate the number of unique stock from old and new sold, we use the same
formula in cell G2 & drag it down and filter 1 in column G.
=IF(COUNTIF(F$2:F2,F2)>1,0,1)
Now, we see that there are 12 unique products sold and those 12 products are sold
& 15 total new & old stocks have been depleted. Now, as the base sheet
is ready, to check the new & old stock sold, we can use the pivot tables.
This shows that the 8 products have been sold from the new stock and 7 products have
been sold from the new stock.
Now, using pivot table we can check the stocks which have been sold under new &
old. As seen above, three stocks have been repeated in new & old stocks – C1072, C2685
& C7019, which explains 12 unique products and 15 old and new stocks.
This analysis or multiple columns unique value counting cannot be done by direct
pivot tables, hence this formula using if and countif will be very helpful to
all.