Counting Unique Entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to count unique entries across a set of colums. For example,

3 4
2 4
4 7
2 1
1 0

I want the result to be 5 as the number of unique entries. Can this be done?

Thanks
 
Assuming that A2:B6 contains the data, try...

=SUM(IF(A2:B6>0,1/COUNTIF(A2:B6,A2:B6)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
Sorry I was not clearer before, but the data is not next to each other. For
example, the first set is in column A and the next set is in column E. Do
you have any suggestions for this? Sorry for the confusion.
 
There may be a better way, but try the following...

=COUNT(1/FREQUENCY((A2:A6,E2:E6),(A2:A6,E2:E6)))-(COUNTIF(A2:A6,0)+COUNTI
F(E2:E6,0)>0)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
I've tried this equation
{=COUNT(1/FREQUENCY((A2:A6,E2:E6),(A2:A6,E2:E6)))-(COUNTIF(A2:A6,0)+COUNTIF(E2:E6,0)>0)} using the CTRL+SHIFT+ENTER, but the results came out to be zero.

Is there another way?
 
Maybe your numbers are being recognized as text. Try...

=ISNUMBER(A2)

If it returns FALSE, try the following...

1) Select an empty cell

2) Edit > Copy

3) Select the range of cells containing your numbers

4) Edit > Paste Special > Add > Ok

Does this help?
 
Very nice!

Are you excluding 0 as a unique entry? If one wanted to count 0 as a unique
entry, I assume you would remove this part at the end?
-(COUNTIF(A2:A6,0)+COUNTIF(E2:E6,0)>0)
 
JMB said:
Are you excluding 0 as a unique entry?
Click to expand...

Yes, as per the example provided by the original post.
If one wanted to count 0 as a unique
entry, I assume you would remove this part at the end?
-(COUNTIF(A2:A6,0)+COUNTIF(E2:E6,0)>0)
Click to expand...

Yes, that's right... :)

Cheers!
 
Back
Top