Counting Formula

[RTB3001]

Hi everyone,

Hope you can help.

Column A has a set of labels, for example AAA, BBB, CCC, DDD, EEE, FFF
These labels can be repeated many times.

Column B has another set of labels 1TTT, 2UUU, 3WWW, 4XXX, 5YYY.
Again these can be repeated.

What I want to know if how many uniques column A has in reference to column B.

i.e.

AAA 1TTT
AAA 1TTT
AAA 1TTT
AAA 2UUU
AAA 2UUU
AAA 5YYY

BBB 3WWW
BBB 3WWW
BBB 4XXX

AAA therefore has 3 uniques
BBB therefore has 2 uniques

Thanks in advance as always, Ross.

 

[NBVC]

Book2
	A	B	C	D	E	F
1	Col A	Col B		AAA	3
2	AAA	1TTT		BBB	2
3	AAA	1TTT
4	AAA	1TTT
5	AAA	2UUU
6	AAA	2UUU
7	AAA	5YYY
8
9	BBB	3WWW
10	BBB	3WWW
11	BBB	4XXX
12
Sheet1

Formula in E1: =COUNT(1/FREQUENCY(IF($A$2:$A$11=D1,IF($B$2:$B$11<>"",MATCH($B$2:$B$11,$B$2:$B$11,0))),ROW($A$2:$A$11)-ROW($A$2)+1))

confirmed with CTRL+SHIFT+ENTER and copied down.

Adjust ranges to suit and re-confirm with CSE key combo.

 

[Scott Huish]

Putting the values you want to check in Column C:
Formula in D1:
=SUM(IF($A$1:$A$1000=C1,1/COUNTIF($B$1:$B$1000,$B$1:$B$1000)))

Confirm with CTRL-SHIFT-ENTER rather than just Enter

Copy down.

Book1
	A	B	C	D
1	AAA	1TTT	AAA	3
2	AAA	1TTT	BBB	2
3	AAA	1TTT
4	AAA	2UUU
5	AAA	2UUU
6	AAA	5YYY
7
8	BBB	3WWW
9	BBB	3WWW
10	BBB	4XXX
Sheet1

 

[arthurbr]

You can also try :
Select your list
Data-Filter-Advanced Filter
Check unique records only
OK

Data - subtotals - Use function Count
OK

 

[RTB3001]

Excellent!

Thanks everyone...

Ross.