Today we are going to discuss about numerous methods in Excel to extract Unique count. We will try to explore methods in classic version, Dynamic spilled array version and LAMBDA helper functions version. Lot to learn, let's dive in.
Method - 1:
Formula in D3 cell is,
=SUMPRODUCT(1/COUNTIF($B$3:$B$15,$B$3:$B$15))
This is simple and straight forward and works in all versions.
Let me start this from scratch,
=COUNTIF($B$3:$B$15,$B$3:$B$15)
Criteria argument expects a single cell value, but here we fed the range B3:B15 with 13 items. So, we urge COUNTIF function to deliver 13 items.
Count of product 'XX' is 3, so this function will place 3 in each of its occurrence.
Count of product 'AA' is 2, so this function will place 2 in each of its occurrence, and so on.
IF we evaluate this COUNTIF part, it will look like this,
{3;2;3;3;3;2;2;1;2;3;3;1;1}
Then, if we divide 1 by this array
=1/{3;2;3;3;3;2;2;1;2;3;3;1;1}
Each occurrence of product 'XX' will become 0.333333 with extraneous decimals,
Each occurrence of product 'AA' will become 0.5, as shown below.
{0.333333333333333;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;1;0.5;0.333333333333333;0.333333333333333;1;1}
Now, SUMPRODUCT will help us to get the sum, in essence to get unique count.
Method - 2:
Formula in D3 cell is,
=SUMPRODUCT(--ISNUMBER(MATCH(ROW($B$3:$B$15)-ROW($B$3)+1,MATCH($B$3:$B$15,$B$3:$B$15,0),0)))
Let me start from scratch,
=MATCH($B$3:$B$15,$B$3:$B$15,0)
In method - 1 we used COUNTIF to get count of each product, here we use MATCH to get the relative position of each product.
Wherever product 'XX' is there, this function will put 1 which is the relative position,
Wherever product 'AA' is there, this function will put 2 which is the relative position, and so on. So, the output will look like below,
={1;2;3;1;3;6;6;8;2;1;3;12;13}
Now, we are going to create sequence of number from 1 to 13. We stop at 13, because of the size of our input range. Then, we will match the sequence of numbers with above array.
Below construction will help us to create sequence of numbers,
=ROW($B$3:$B$15)-ROW($B$3)+1
if we match this sequence of numbers with above array,
=MATCH({1;2;3;4;5;6;7;8;9;10;11;12;13},{1;2;3;1;3;6;6;8;2;1;3;12;13},0)
Positions of 4,5,7.. are filled with duplicates in lookup array, so this function will throw error at these positions,
={1;2;3;#N/A;#N/A;6;#N/A;8;#N/A;#N/A;#N/A;12;13}
Numbers are positions of first occurrence of items and errors are positions of duplicate items. ISNUMBER function will convert this into TRUE and FALSE and double negative will convert them into ones and zeroes.
Finally, SUMPRODUCT function will help us to sum and in essence will deliver us the Unique count.
Method - 3:
=SUMPRODUCT(--(FREQUENCY(ROW($B$3:$B$15)-ROW($B$3)+1,MATCH($B$3:$B$15,$B$3:$B$15,0))>0))
This is same as method - 2 but with the help of frequency function.
Method - 4:
=COUNT(MODE.MULT(IFERROR(MATCH(ROW($B$3:$B$15)-ROW($B$3)+1,MATCH($B$3:$B$15,$B$3:$B$15,0),{0,0}),"")))
Method - 5:
=COUNTA(UNIQUE($B$3:$B$15))
This works in Excel versions 2019 or above.
UNIQUE function helps us to extract unique items and COUNTA function helps us to count non-blank cells.
Method - 6:
=COUNT(UNIQUE(MATCH($B$3:$B$15,$B$3:$B$15,0)))
Method - 7:
=LET(Rng,$B$3:$B$15,COUNTA(FILTER(Rng,ISNUMBER(MATCH(SEQUENCE(ROWS(Rng)),
MATCH(Rng,Rng,0),0)))))
This works in Excel versions 2019 or above.
Method - 8:
=REDUCE(0,B3:B15,LAMBDA(a,v,SUM(a,--(COUNTIF(v:B3,v)=1))))
This will only work in Office 365.
Method - 9:
=SUM(SCAN(0,B3:B15,LAMBDA(a,v,--(COUNTIF(v:B3,v)=1))))
Comments