Count Unique Values with a Criteria

[Chris Gorham]

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

 

[Bob Phillips]

=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)

 

[Domenic]

First, download and install the free add-in Morefunc.xll. Then,
assuming that A2:B9 contains the data, let D24 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!