# Thread: Unique Values in a Column (2000)

1. ## Unique Values in a Column (2000)

Is there an easy way to determine the number of unique bits of text in a column (or part of an area) without having a separate list of what *could* be in the column. ?

For example:

Column A

Apple
Apple
Apple
Pear
Apple
Pear
Grape
Grape
Grape
Apple
Pear
Apple

Answer is 3 (Apples, Pears, and Grapes)

2. ## Re: Unique Values in a Column (2000)

This famous array formula does that:

=SUM(1/COUNTIF(A1:A10,A1:A10))
Hit control-shift-enter in stead of enter.

3. ## Re: Unique Values in a Column (2000)

Steve,

Let's say that your data are in range A1:A37. Enter the following formula in the cell that should contain the number of unique items:

=SUM(1/COUNTIF(A1:A37,A1:A37))

This is an array formula. i.e. you must confirm it with Ctrl+Shift+Enter instead of just Enter.

Remark: there should be no gaps (empty cells) in the range.

4. ## Re: Unique Values in a Column (2000)

Ahhh, there are empty cells in the range.... Is there an easy way of getting around this problem other than copying them all to another range of cells ?

5. ## Re: Unique Values in a Column (2000)

Try

=SUM(IF(COUNTIF(A1:A37,A1:A37)=0,0,1/COUNTIF(A1:A37,A1:A37)))

Again, entered as an array formula (Ctrl+Shift+Enter)

6. ## Re: Unique Values in a Column (2000)

It works Hans.. kind of....

=SUM(IF(COUNTIF(AH22:AH1000,AH22:AH1000)=0,0,1/COUNTIF(AH22:AH1000,AH22:AH1000)))

And the result is : 2.0553047404

There are two unique values in the range. experimenting atm with more reveals the first number is always correct.. so can just Int it or
round it etc...

Many thanks.

7. ## Re: Unique Values in a Column (2000)

Strange, the difference is too large to ascribe to rounding errors. It works without rounding errors for quite large ranges on my system <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

8. ## Re: Unique Values in a Column (2000)

Did you remember to confirm with ctrl-shift-enter?
Press <F2> fir edit mode and confirm with ctrl-shift-enter
Steve

9. ## Re: Unique Values in a Column (2000)

Steve, yes it puts curly brackets around the whole formula..

Hans, yea its a bit strange, but i tested it with various amounts that i would encounter, and if do -int(cell) then its giving me the number i expect.

You only get half a beer for that one though Hans cos its not perfect <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

10. ## Re: Unique Values in a Column (2000)

Do you have a "Null string" ("")in your data set (either a formula that returns a null string, or even a cell (non-blank) that STARTS with a single quote (') so it looks BLANK but isn't, in addition to truly blank cells?

This will "screw up the values" since Hans "fixed the div/0 error of the 1/0, BUT the null string will count all the BLANK CELLS so its count is off!

Try:
<pre>=SUM(IF(LEN(AH22:AH1000)=0,0,1/COUNTIF(AH22:AH1000,AH22:AH1000)))</pre>

Steve

11. ## Re: Unique Values in a Column (2000)

1]

=SUM(IF(A2:A13<>"",1/COUNTIF(A2:A13,A2:A13)))

=SUM(IF(LEN(A2:A13),1/COUNTIF(A2:A13,A2:A13)))

2]

=COUNDIFF(A2:A13)

3]

=SUMPRODUCT((A2:A13<>"")/COUNTIF(A2:A13,A2:A13&""))

The formula in [1] must be array-entered. It's an elaboration of David Hager's

=SUM(1/COUNTIF(Range,Range))

in order to cope with empty cells and formula-blanks [ blanks generated by formulas such as =IF(D1,1,"") ]

The formula in [2] uses a function from Longre's morefunc add-in. It's fast and normally entered. It counts the formula-blanks as a separate entity.

The one in [3], again normally-entered, is due to Harlan Grove. Like [1], it can cope with empty cells and formula-blanks, that is, it treats them as alike.

12. ## Re: Unique Values in a Column (2000)

Not sure what's happening, but zeros seem to behave oddly.

With the following formula in some convenient cell
<pre>=SUM(IF(COUNTIF(A1:A1000,A1:A1000)=0,0,1/COUNTIF(A1:A1000,A1:A1000)))</pre>

and data only in the first 10 rows of column A as follows:<pre>0
a
s
d
f
g
0
f
0
0

</pre>

I get a value of 253.5

Odd!

Ian.

13. ## Re: Unique Values in a Column (2000)

(Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

See

<post#=271039>post 271039</post#>

14. ## Re: Unique Values in a Column (2000)

All the empty cells match the Zero as they also match the the null strings.

As I mentioned in <post#=270357>post 270357</post#>

<pre>=SUM(IF(LEN(A1:A1000)=0,0,1/COUNTIF(A1:A1000,A1:A1000)))</pre>

Should work fine even with the zeroes.

Steve

15. ## Re: Unique Values in a Column (2000)

Thanks!

Ian

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•