Count how many different text values in an array.

G

Guest

I want to count the number of different text values in an unsorted column.
EXAMPLE:
I have a variety of text values in column 'A', for instance the 'city' value
of a persons address. And I have 5000 rows of data in the spreadsheet,
random people from all over the country.
I want to know how many different cities are represented by the random group
of people.

Is there a way to "COUNT" an array but count each unique value only once.
 
R

Ragdyer

Try this:

=SUMPRODUCT((A1:A30<>"")/COUNTIF(A1:A30,A1:A30&""))

With SumProduct, you *cannot* use an entire column reference (A:A).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count values, excluding duplicates and specific values 2
Counting 3
count unique values in one column for a set value in another col. 2
Count Unique Values 4
array formula 2
count unique values if 2 variables 6
Count Unique Values in 1 Column based on Date Range in another Column 28
Count Unknown Text 2

Top