Counting with out Duplicates
Posted by Lewis on February 02, 2002 12:15 PM
Hey all
If I want to count a number of cells in cells in D11:D36 with out counting the duplicates, how would I go about it?
say the data read
NY, NY, NY, NY, CA, CA, CA
the result would be 2
thanks
-Lewis
Posted by Aladin Akyurek on February 02, 2002 12:21 PM
A frequently asked question. The array-formula combines Hager's formula with LEN for eventual true blanks & formula generated blanks in the range of interest:
=SUM(IF(LEN(D11:D36),1/COUNTIF(D1:D36,D1:D36)))
In order to array-enter a formula you need to hit control+shift=enter at the same time, not just enter.
===========
