Count of unique values
 

I have 2 columns of data, A1:A10 has just numbers eg 123, 456 etc, B1:B10 has
a combination of letters and numbers eg ABC123, DEF456 etc. The data is not
continous within each column (ie there are blank rows within each range).

I need a formula for each range which will count the number of unique values
within each column. I have tried to find solution in similar Q&As but can't
seem to find a solution which will disregard the empty cells and not return a
#DIV/0! result.

Any help would be great. Thanks.
--
Regards
Matt

Count of unique values
 

=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))

--
__________________________________
HTH

Bob

"Matt" wrote in message
...
I have 2 columns of data, A1:A10 has just numbers eg 123, 456 etc, B1:B10
has
a combination of letters and numbers eg ABC123, DEF456 etc. The data is
not
continous within each column (ie there are blank rows within each range).

I need a formula for each range which will count the number of unique
values
within each column. I have tried to find solution in similar Q&As but
can't
seem to find a solution which will disregard the empty cells and not
return a
#DIV/0! result.

Any help would be great. Thanks.
--
Regards
Matt

Count of unique values
 

Matt

Put this in a cell and array enter. See below for array instructions. Drag 1
column to the right for column B

=SUM(COUNTIF(A1:A10,A1:A10)/IF(NOT(COUNTIF(A1:A10,A1:A10)),1,COUNTIF(A1:A10,A1 :A10))^2)

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Matt" wrote:

I have 2 columns of data, A1:A10 has just numbers eg 123, 456 etc, B1:B10 has
a combination of letters and numbers eg ABC123, DEF456 etc. The data is not
continous within each column (ie there are blank rows within each range).

I need a formula for each range which will count the number of unique values
within each column. I have tried to find solution in similar Q&As but can't
seem to find a solution which will disregard the empty cells and not return a
#DIV/0! result.

Any help would be great. Thanks.
--
Regards
Matt

Count of unique values
 

In say, A12:
=SUMPRODUCT(--(A1:A10<""),1/COUNTIF(A1:A10,A1:A10&""))
Copy A12 to B12
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Matt" wrote:
I have 2 columns of data, A1:A10 has just numbers eg 123, 456 etc, B1:B10 has
a combination of letters and numbers eg ABC123, DEF456 etc. The data is not
continous within each column (ie there are blank rows within each range).

I need a formula for each range which will count the number of unique values
within each column. I have tried to find solution in similar Q&As but can't
seem to find a solution which will disregard the empty cells and not return a
#DIV/0! result.

Any help would be great. Thanks.

Count of unique values
 

Thanks Mike.

Works great.

--
Regards
Matt


"Mike H" wrote:

Matt

Put this in a cell and array enter. See below for array instructions. Drag 1
column to the right for column B

=SUM(COUNTIF(A1:A10,A1:A10)/IF(NOT(COUNTIF(A1:A10,A1:A10)),1,COUNTIF(A1:A10,A1 :A10))^2)

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Matt" wrote:

I have 2 columns of data, A1:A10 has just numbers eg 123, 456 etc, B1:B10 has
a combination of letters and numbers eg ABC123, DEF456 etc. The data is not
continous within each column (ie there are blank rows within each range).

I need a formula for each range which will count the number of unique values
within each column. I have tried to find solution in similar Q&As but can't
seem to find a solution which will disregard the empty cells and not return a
#DIV/0! result.

Any help would be great. Thanks.
--
Regards
Matt

Count of unique values
 

Matt,
Just pondering quietly why you weren't able to get Bob's & my response to
work for you. All 3 responses work ok in light testing over here. Any clues?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Matt" wrote:
Thanks Mike.
Works great.

Count of unique values
 

Thanks Bob

Works great.

--
Regards
Matt


"Bob Phillips" wrote:

=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))

--
__________________________________
HTH

Bob

"Matt" wrote in message
...
I have 2 columns of data, A1:A10 has just numbers eg 123, 456 etc, B1:B10
has
a combination of letters and numbers eg ABC123, DEF456 etc. The data is
not
continous within each column (ie there are blank rows within each range).

I need a formula for each range which will count the number of unique
values
within each column. I have tried to find solution in similar Q&As but
can't
seem to find a solution which will disregard the empty cells and not
return a
#DIV/0! result.

Any help would be great. Thanks.
--
Regards
Matt

Count of unique values
 

Thanks Max

Works great. Sorry I didn't get back to you earlier. All 3 answers work
fine.

--
Regards
Matt


"Max" wrote:

In say, A12:
=SUMPRODUCT(--(A1:A10<""),1/COUNTIF(A1:A10,A1:A10&""))
Copy A12 to B12
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Matt" wrote:
I have 2 columns of data, A1:A10 has just numbers eg 123, 456 etc, B1:B10 has
a combination of letters and numbers eg ABC123, DEF456 etc. The data is not
continous within each column (ie there are blank rows within each range).

I need a formula for each range which will count the number of unique values
within each column. I have tried to find solution in similar Q&As but can't
seem to find a solution which will disregard the empty cells and not return a
#DIV/0! result.

Any help would be great. Thanks.

Count of unique values
 

Welcome, and thanks for a good closure to this thread.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Matt" wrote in message
...
Thanks Max

Works great. Sorry I didn't get back to you earlier. All 3 answers work
fine.

--
Regards
Matt