In our last post, we discussed about the different methods to remove duplicates,
Here we are going to discuss about different methods to get unique count.
So, let's dive into the topic without any further delay.
You can see the names of excel experts with duplicates in it, in which we are going to get the unique count.
Method 1:
Combination of SUMPRODUCT and COUNTIF functions,
The formula in D4 cell is,
=SUMPRODUCT(1/COUNTIF($B$4:$B$16,$B$4:$B$16))
First, we will discuss about COUNTIF function and what it does here.
Here, we fed the range B4:B16 for both range and criteria argument in COUNTIF function. This will pick "Mike Girvin" from criteria range and will count the number of occurrences in the range B4:B16 and will throw the value as 2. It does the same for each criteria, if we evaluate the COUNTIF portion by pressing F9, the result will look like the below pic.
Here, we could see that the name "Leila Gharani" appears 3 times, so in each of its occurrence it evaluates to 3. When we divide 1 by this array, each relative position of "Leila Gharani" will result in 1/3. If we evaluate the division and countif portion, the result will look like below pic.
If it occurs 2 times in the range then it will carry 0.5 in each of its occurrence,
if it occurs 3 time in the range then it will carry 0.33333333 (extraneous decimal) in each of its occurrence, then the sumproduct function will sum the result and will throw the count of unique appearance, in our case the unique count is 7.
Method 2:
Combination of SUMPRODUCT and MATCH function, bit wild but effective.
The formula in F4 cell is,
=SUMPRODUCT(ISNUMBER(MATCH(ROW($B$4:$B$16)-ROW($B$4)+1,MATCH($B$4:$B$16,$B$4:$B$16,0),0))*1)
First, we will discuss about the lookup value argument of the MATCH function, this is the construction used to create the sequence of numbers inside the formula,
Our data contains 13 rows, so the plan is to create the sequence of numbers from 1 to 13.
ROW function delivers the row number of a cell or range, here we fed range from $B$4:$B$16, row numbers of this range is from 4 to 16, it will generate the sequence from 4 but our requirement is to start from 1 and so we detect the row of cell $B$4 which is 4. If we detect 4 from 4 then the sequence will start from 0 and so we add 1. Below are the pics of evaluation. The result of this lookup value is the numbers from 1 to 13.
Now we will look into the lookup_array argument, where we used MATCH function to construct the array, the lookup_value and lookup_array of the inner match arguments is fed with same range of B4:B16. So, MATCH will take each lookup_value and get the relative position after doing exact match lookup into lookup_array. If we evaluate the lookup array argument of outer match function, it will look like below.
Now the sequence of numbers from 1 to 13 will be looked up in the above evaluated array to get the exact match, Then the result will show the relative position of first occurrence of each unique item rest will be filled with #N/A error as shown below.
We wrapped this array in the ISNUMBER function, which will deliver TRUE for the numbers and FALSE for the other Not available error values. Multiplication by 1 is done to do some mathematical operation to convert TRUE into 1 and FALSE in to 0. We can even add 0 or divide by 1. If we add double negative before the ISNUMBER function, it does the conversion from Boolean into 1's and 0's and this is considered to be the fastest method.
If we sum this, we will get the unique count as 7.
Comments