# Formula to count the number of unique text values from a list.

1. ## Formula to count the number of unique text values from a list.

Hello Everyone,

Just joined this forum.

I Googled out the below formula that counts the number of unique text values from a list. While it does work perfectly fine, but i am unable to understand the logic working behind this formula, especially the reason for using SUMPRODUCT function and A:A&"".

=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""))-1

Any explanation will be greatly appreciated.  Register To Reply

2. ## Re: Formula to count the number of unique text values from a list.

welcome to the forum, PritishD. first of all, you shouldn't be using the whole column if you are using array formulas & SUMPRODUCT is one of them. reason is because it will slow down the workbook as such formulas work very hard.

to illustrate, here's some data to explain better:
Data Range
 A 1 Name 2 A 3 B 4 5 A 6 A 7 8 B 9 B 10 C

=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))
not sure why the minus 1 is there. is it for your header? so first select this portion in the formula bar & press F9 to calculate
(A2:A10<>"")
you don't want to count blanks as a unique value. so the above will give you a bunch of TRUEs & FALSEs on whether those cells does not equal to blanks
{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}
so A2 is not. A3 is not. A4 is a blank. and so on

not sure if you know 1/0 in Excel gives you an error #DIV/0. and if you have a blank cell, excel returns a 0. you can try highlighting this in the formula bar & press F9 to see:
A2:A10
{"A";"B";0;"A";"A";0;"B";"B";"C"}

so the A2:A10&"" prevents that. it makes those zeroes become blanks. try highlighting this portion & press F9
A2:A10&""
{"A";"B";"";"A";"A";"";"B";"B";"C"}

now select this portion & press F9
COUNTIF(A2:A10,A2:A10&"")
it will give you multiple results like this (9 results to be exact)
{3;3;2;3;3;2;3;3;1}
what it does is to count how many times A2 appeared in A2:A10. that will give you 3. cause "A" appeared 3 times.
it then counts how many times A3 appeared in A2:A10. that will still give you 3. cause "B" appeared 3 times.
counts how many times A4 appeared in A2:A10. that will still give you 2. cause "" appeared 2 times.
and so on.

so you have the 1st part of TRUEs & FALSEs (which Excel sees as 1 & 0) divided by the 2nd part of how many times each cell appears.
{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}
divided by:
{3;3;2;3;3;2;3;3;1}
so that's 1/3, 1/3, 0/2, 1/3... and so on
that gives you a fraction of each cells. try selecting this section & press F9
(A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")
{0.333333333333333;0.333333333333333;0;0.333333333333333;0.333333333333333;0;0.333333333333333;0.333333333333333;1}
so that's actually telling you A2 is 0.33 (1/3) of how the total times "A" appeared.
A3 is also 1/3 of how many times "B" appeared
A4 is 0.
and so on...

as the above are multiple array results of 1/3, zeroes & 1, you need a function to add them up. that is the SUMPRODUCT. so if you have 3 "A"s, that is 1/3 + 1/3 + 1/3. 3 "B"s is the same. that gives you 2. "C" is 1 as it only appeared once. that is a total of 3 unique values.

hope that helps  Register To Reply

3. ## Re: Formula to count the number of unique text values from a list.

I see there still is confusion between unique and distinct values...
Distinct values = all values whether they appear once or more
Unique values = values that appear only once  Register To Reply

4. ## Re: Formula to count the number of unique text values from a list.

Thanks benishiryo. And yes, that -1 is for the header.  Register To Reply