Re: Counting Unique Values


"Epinn" <someone@xxxxxxxxxxxxxxxxxxx> wrote in message
news:%23TqQCEn9GHA.3960@xxxxxxxxxxxxxxxxxxxxxxx
Biff,

That's brilliant! Some time ago, I found the following array formula
suggested by an expert.


{=SUM(COUNTIF(A1:A15,A1:A15)/IF(NOT(COUNTIF(A1:A15,A1:A15)),1,COUNTIF(A1:A15
,A1:A15))^2)}

Like your formula, it takes care of blanks in the array as well.
Can you confirm that both your formula and the above formula do
exactly the same thing i.e. count unique text and numeric values in
a range which may contain blanks. If yes, I'll replace the above with
your formula.


They are essentially the same formula. You can simplify it by removing the ^
2 and testing for blan k

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

which starts to look more like the SP version.



.

Relevant Pages