Excel countif to Count unique values in a column

Learn to count the number of unique values in a column using Countif formula in MS Excel. Then prepare summaries when multiple columns have duplicate values which cannot be done by a normal pivot table in direct form.

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.

By Tarun Vir   Popularity  (2398 Views)