shantraj.antin@gmail.com Member Jan 27, 2017 #1 Hi All, I have set of data in a Column. I Am looking for a formula to identify the count of Unique values from that column. Attached is the Sample file for your reference. Attachments Sample File.xlsx 12.2 KB · Views: 11
Hi All, I have set of data in a Column. I Am looking for a formula to identify the count of Unique values from that column. Attached is the Sample file for your reference.
B bosco_yip Excel Ninja Jan 27, 2017 #2 Try……… Count of Unique Values, in F3 formula : =SUMPRODUCT(1/COUNTIF(B2:B14,B2:B14)) If data have blank cells, then use this : =SUMPRODUCT((B2:B100<>"")/COUNTIF(B2:B100,B2:B100&"")) Regards
Try……… Count of Unique Values, in F3 formula : =SUMPRODUCT(1/COUNTIF(B2:B14,B2:B14)) If data have blank cells, then use this : =SUMPRODUCT((B2:B100<>"")/COUNTIF(B2:B100,B2:B100&"")) Regards
John Jairo V Well-Known Member Jan 27, 2017 #3 Hi! You could use: =SUM(N(FREQUENCY(MATCH(B2:B14,B2:B14,),ROW(B2:B14)-ROW(B1))>0)) =SUMPRODUCT(1/COUNTIF(B2:B14,B2:B14)) If you have blanks cell, could use: =SUMPRODUCT((B2:B14<>"")/COUNTIF(B2:B14,B2:B14&"")) Blessings!
Hi! You could use: =SUM(N(FREQUENCY(MATCH(B2:B14,B2:B14,),ROW(B2:B14)-ROW(B1))>0)) =SUMPRODUCT(1/COUNTIF(B2:B14,B2:B14)) If you have blanks cell, could use: =SUMPRODUCT((B2:B14<>"")/COUNTIF(B2:B14,B2:B14&"")) Blessings!
John Jairo V Well-Known Member Jan 27, 2017 #4 jejeje... almost the same answer @bosco_yip. Blessings!
B bosco_yip Excel Ninja Jan 27, 2017 #5 In addition to John's first formula. If data have blank cells, use this : =SUM(N(FREQUENCY(MATCH(B2:B100&"",B2:B100&"",)*(B2:B100<>""),ROW(B2:B100)-ROW(B1))>0)) Regards
In addition to John's first formula. If data have blank cells, use this : =SUM(N(FREQUENCY(MATCH(B2:B100&"",B2:B100&"",)*(B2:B100<>""),ROW(B2:B100)-ROW(B1))>0)) Regards