Counting Unique Entries within an Array calculation

[Vance36]

Hey Guys,

I've been getting frustrated with this all day & I am convinced there must be a simple solution. I am working with several columns in a spreadsheet & I require certain criteria in several columns to force results onto a summary sheet. I have managed to complete this task with Array formulas but as soon as I attach the Frequencey formula to the Array it falls down. I'll attempt to explain with some random data below:


A B
1 Agent Name Ref
2 Jon Todd 123456
3 Jack Jones 45
4 Jon Todd 123456
5 Jon Todd 21
6 Jim Reeves 123
7 Jim Reeves 123

Ok so I want to see each name only once with a count of the unique Ref's. Like this:

Jon Todd = 2 '(123456 & 21)
Jack Jones = 1 '(45)
Jim Reeves = 1 '(123)

So my array will allow me to show the agent name but will only sum up the ref's it count see the unique values. I could do this a number of ways such as with advanced filters but then I can't work it into the array. It has to be part of the array as I can't change the source data in any way. The frequency formula works but not within an array.

My task is 99% done & this final part is frustrating me no end.

Any views on this would be greatfully received.

 

[Robert Mika]

If your unique names are in column C starts from C2 put this in D2

=COUNT(1/FREQUENCY(IF($A$2:$A$16=C2,MATCH($B$2:$B$16,$B$2:$B$16,0)),ROW($A$2:$A$16)-ROW(AA2)+1))

Confirm C+S+E ,copy down.

 

[Vance36]

Cheers Robert,

That was exactly the result I required, great job mate.

 

[T. Valko]

Hey Guys,

I've been getting frustrated with this all day & I am convinced there must be a simple solution. I am working with several columns in a spreadsheet & I require certain criteria in several columns to force results onto a summary sheet. I have managed to complete this task with Array formulas but as soon as I attach the Frequencey formula to the Array it falls down. I'll attempt to explain with some random data below:


A B
1 Agent Name Ref
2 Jon Todd 123456
3 Jack Jones 45
4 Jon Todd 123456
5 Jon Todd 21
6 Jim Reeves 123
7 Jim Reeves 123

Ok so I want to see each name only once with a count of the unique Ref's. Like this:

Jon Todd = 2 '(123456 & 21)
Jack Jones = 1 '(45)
Jim Reeves = 1 '(123)

So my array will allow me to show the agent name but will only sum up the ref's it count see the unique values. I could do this a number of ways such as with advanced filters but then I can't work it into the array. It has to be part of the array as I can't change the source data in any way. The frequency formula works but not within an array.

My task is 99% done & this final part is frustrating me no end.

Any views on this would be greatfully received.

If the "REF" really is a number...

Book1
	A	B	C	D	E
2	Jon Todd	123456	_	Jon Todd	2
3	Jack Jones	45	_	Jack Jones	1
4	Jon Todd	123456	_	Jim Reeves	1
5	Jon Todd	21	_	_	_
6	Jim Reeves	123	_	_	_
7	Jim Reeves	123	_	_	_
Sheet1

This array formula** entered in E2 and copied down:

=SUM(IF(FREQUENCY(IF(A$2:A$7=D2,B$2:B$7),B$2:B$7),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Ron Coderre]

Using the example posted by T. Valko (Biff)
This regular formula (no C+S+E needed) counts the unique combinations of the referenced name and the number

E2: =COUNT(INDEX(1/((A$2:A$7=D2)*FREQUENCY((A$2:A$7=D2)*B$2:B$7,B$2:B$7)),0))

Copy that formula down as far as you need.

Is that something you can work with?