Formula to count the number of different values in a range

I'm looking for a formula that will give me the number of different values
in a range.

Example: Column A may have five cells that are "4", five cells that are
"7", five cells that are "9". Of the fifteen cells that contain data, there
are only 3 different values. I'd like to use a formula that will count the
number of different values in column A, in this case the result is "3".

Thanks,
Paul

Try...

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

OR

=SUM(IF(A1:A15<>"",1/COUNTIF(A1:A15,A1:A15)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <[email protected]>,
"PCLIVE" <[email protected]> wrote:

> I'm looking for a formula that will give me the number of different values
> in a range.
>
> Example: Column A may have five cells that are "4", five cells that are
> "7", five cells that are "9". Of the fifteen cells that contain data, there
> are only 3 different values. I'd like to use a formula that will count the
> number of different values in column A, in this case the result is "3".
>
> Thanks,
> Paul

Wow.

That's a more complicated formula that I expected. Nevertheless, it seems
to work.

Thanks for your help,
Paul


"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>
> OR
>
> =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A15,A1:A15)))
>
> ...confirmed with CONTROL+SHIFT+ENTER.
>
> Hope this helps!
>
> In article <[email protected]>,
> "PCLIVE" <[email protected]> wrote:
>
>> I'm looking for a formula that will give me the number of different
>> values
>> in a range.
>>
>> Example: Column A may have five cells that are "4", five cells that are
>> "7", five cells that are "9". Of the fifteen cells that contain data,
>> there
>> are only 3 different values. I'd like to use a formula that will count
>> the
>> number of different values in column A, in this case the result is "3".
>>
>> Thanks,
>> Paul