Count unique text values
That worked - thanks!
"Gary''s Student" wrote:
=SUM(1/COUNTIF(A1:A10,A1:A10))-1
and confirm as an array formula (use CNTRL-SHFT-ENTER rather than just ENTER)
will display 3 for your example.
--
Gary''s Student
"doublew" wrote:
I have a column of data that looks something like this:
Spanish
0
0
French
0
German
French
0
0
Spanish
I want to insert a formula at the bottom of the column to give me the total
number of unique languages (that is, a count of all the unique values,
ignoring zeros).
I have tried using both of the following formulas (which I don't really
understand so feel free to point out the obvious), but both include the zeros
in the count:
=SUMPRODUCT((D3:D77<"")/COUNTIF(D3:D77,D3:D77&""))
AND
=SUM(IF(FREQUENCY(MATCH(D3:D77,D3:D77,0),MATCH(D3: D77,D3:D77,0))0,1))
where D3:D77 is the range of data I am working with.
The reason I have zeros in my column is that the cells already contain an IF
formula which populates with the language name if column B contains a 1 and
populates with a 0 otherwise, i.e:
=IF(B3=1,"Spanish",0)
Can anyone help?
|