software

I am trying to count unique entries across a set of colums. For example,
A C
3 4
2 4
4 7
2 1
1 0

I want the result to be 5 as the number of unique entries. The entries are
not in consecutives column.

ThanksYou want the count of unique items for each column?

=SUM(IF(A1:A6lt;gt;quot;quot;,1/COUNTIF(A1:A6,A1:A6)))

confirmed with Control Shift Enter.

Copy across to other columns.

quot;SouthCarolinaquot; wrote:

gt; I am trying to count unique entries across a set of colums. For example,
gt; A C
gt; 3 4
gt; 2 4
gt; 4 7
gt; 2 1
gt; 1 0
gt;
gt; I want the result to be 5 as the number of unique entries. The entries are
gt; not in consecutives column.
gt;
gt; Thanks
gt;

Upon rereading, I am thinking you want the number of unique entries in all
columns combined. You could try a UDF. Paste into an excel module and call
it like

=Unique(A1:A5, C1:C5)

Function Unique(ParamArray Rng() As Variant)
Dim i As Long
Dim t As Long
Dim x As Range
Dim Temp As Collection

On Error Resume Next
Set Temp = New Collection

For i = 0 To UBound(Rng())
For t = 1 To Rng(i).Areas.Count
For Each x In Rng(i).Areas(t).Cells
Temp.Add Trim(x.Value), CStr(Trim(x.Value))
Next x
Next t
Next i

Unique = Temp.Count

End Functionquot;SouthCarolinaquot; wrote:

gt; I am trying to count unique entries across a set of colums. For example,
gt; A C
gt; 3 4
gt; 2 4
gt; 4 7
gt; 2 1
gt; 1 0
gt;
gt; I want the result to be 5 as the number of unique entries. The entries are
gt; not in consecutives column.
gt;
gt; Thanks
gt;

JMB wrote...
gt;Upon rereading, I am thinking you want the number of unique entries in all
gt;columns combined. You could try a UDF. Paste into an excel module and call
gt;it like
....
gt;quot;SouthCarolinaquot; wrote:
gt;gt;I am trying to count unique entries across a set of colums. For example,
gt;gt;A C
gt;gt;3 4
gt;gt;2 4
gt;gt;4 7
gt;gt;2 1
gt;gt;1 0
gt;gt;
gt;gt;I want the result to be 5 as the number of unique entries. The entries are
gt;gt;not in consecutives column.

Why 5? Col A contains 4 distinct numbers: 3, 2, 4 and 1. Col C contains
two mo 7 and 0. Should the zero not be included? If it should be
included, there are 6 distinct values.

This is just conditional counting, so there's no need for udfs.

=COUNT(1/FREQUENCY((A1:A5,C1:C5),(A1:A5,C1:C5)))gt; This is just conditional counting, so there's no need for udfs.

Only if I was a bit smarter lt;ggt;

quot;Harlan Grovequot; wrote:

gt; JMB wrote...
gt; gt;Upon rereading, I am thinking you want the number of unique entries in all
gt; gt;columns combined. You could try a UDF. Paste into an excel module and call
gt; gt;it like
gt; ....
gt; gt;quot;SouthCarolinaquot; wrote:
gt; gt;gt;I am trying to count unique entries across a set of colums. For example,
gt; gt;gt;A C
gt; gt;gt;3 4
gt; gt;gt;2 4
gt; gt;gt;4 7
gt; gt;gt;2 1
gt; gt;gt;1 0
gt; gt;gt;
gt; gt;gt;I want the result to be 5 as the number of unique entries. The entries are
gt; gt;gt;not in consecutives column.
gt;
gt; Why 5? Col A contains 4 distinct numbers: 3, 2, 4 and 1. Col C contains
gt; two mo 7 and 0. Should the zero not be included? If it should be
gt; included, there are 6 distinct values.
gt;
gt; This is just conditional counting, so there's no need for udfs.
gt;
gt; =COUNT(1/FREQUENCY((A1:A5,C1:C5),(A1:A5,C1:C5)))
gt;
gt;