Count Unique items per Categ

Hi all... not sure if this is best handled via formula or vba code, but am pulling my hair out trying to collect some metrics for a weekly report. I would assume that because of the potential volume, vba would be a bit faster, but I'm not concerned with speed at this point.

I'm attaching a sample worksheet in case the boatloads of sinus medication I've had today renders me unable to clearly explain what I'm trying to do.

You will see that there are two columns in my sample set. ID (which can vary from one to hundreds of thousands) and Category (which will always be the same 6 values), neither of which are unique. I need to be able to provide a count of unique ID's per category - almost the opposite of what a countifs would give.

So even though ID NU0P2KBE07I9 is listed many times, 3 of which are for category "Topics-Important" I can only count it ONCE for "Topics-Important"

Hope that makes sense....

Thank you for any help!

EDIT: I apologize - there was a database error so I was unable to access the post again, then lost my internet connection at home for most of the weekend. I have attached the file.

Unfortunately I see no attachment to your post

Try something like this...

Data Range

	A	B	C	D	E
1	ID	Cat		Cat	Unique IDs
2	4	Cat1		Cat1	2
3	7	Cat1		Cat2	1
4	7	Cat2		Cat3	2
5	3	Cat3		Cat4	3
6	5	Cat3		Cat5	2
7	5	Cat3
8	4	Cat4
9	7	Cat4
10	10	Cat4
11	10	Cat4
12	3	Cat5
13	3	Cat5
14	3	Cat5
15	9	Cat5
16	------	------	------	------	------

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

=SUM(IF(FREQUENCY(IF(B$2:B$15=D2,MATCH(A$2:A$15,A$2:A$15,0)),ROW(A$2:A$15)-ROW(A$2)+1),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.

Use dynamic named ranges...

http://www.contextures.com/xlNames01.html#Dynamic

g2

Please Login or Register  to view this content.

try the above and copy towards down
but it will take much time to evaluate formula (to give result)

Originally Posted by nflsales

but it will take much time to evaluate formula (to give result)

That formula is very inefficient.

Originally Posted by Tony Valko

That formula is very inefficient.

May I know where is the wrong in the formula