Count Unique Values with a Criteria
 

Hi,

I have 2 columns of data...as follows

Col A Col B

0001 123
0001 123
0001 111
0002 146
0002 146
0003 234
0003 234
0003 100

I want to be able to count the number of unique entries in Col B using Col A
as the criteria....

Answer

0001 2
0002 1
0003 2

and so on....

Probably an array function but not sure....any help appreciated

Chris

Count Unique Values with a Criteria
 

=SUM(1*(FREQUENCY(IF((A1:A10<"")*(A1:A10="0001"), B1:B10),B1:B10)0))

this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Chris Gorham" wrote in message
...
Hi,

I have 2 columns of data...as follows

Col A Col B

0001 123
0001 123
0001 111
0002 146
0002 146
0003 234
0003 234
0003 100

I want to be able to count the number of unique entries in Col B using Col
A
as the criteria....

Answer

0001 2
0002 1
0003 2

and so on....

Probably an array function but not sure....any help appreciated

Chris

Count Unique Values with a Criteria
 

First, download and install the free add-in Morefunc.xll. Then,
assuming that A2:B9 contains the data, let D2:D4 contain 0001, 0002, and
003, then try the following formula which needs to be confirmed with
CONTROL+SHIFT+ENTER...

E2, copied down:

=COUNTDIFF(IF($A$2:$A$9=D2,IF($B$2:$B$9<"",$B$2:$ B$9)),,FALSE)

The add-in can be found here...

http://xcell05.free.fr/

Hope this helps!

In article ,
Chris Gorham wrote:

Hi,

I have 2 columns of data...as follows

Col A Col B

0001 123
0001 123
0001 111
0002 146
0002 146
0003 234
0003 234
0003 100

I want to be able to count the number of unique entries in Col B using Col A
as the criteria....

Answer

0001 2
0002 1
0003 2

and so on....

Probably an array function but not sure....any help appreciated

Chris