Count how many different text values in an array.
 

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.

Try this:

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

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"OVERLOAD" wrote in message
...
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.

That worked......thanks........

"Ragdyer" wrote:

Try this:

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

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"OVERLOAD" wrote in message
...
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.

Appreciate the feed-back.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"OVERLOAD" wrote in message
...
That worked......thanks........

"Ragdyer" wrote:

Try this:

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

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

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-

"OVERLOAD" wrote in message
...
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.