Help counting distinct Records

Hi I have an excel table that looks something like this

ColA ColB
==== ====
1111 10-2011
1111 10-2011
2222 10-2011
2222 10-2011
3333 10-2011
2222 11-2011
1111 11-2011
2222 11-2011
4444 11-2011
1111 11-2011
2222 11-2011
3333 12-2011
3333 12-2011
2222 12-2011
6666 12-2011
1111 12-2011
1111 12-2011
3333 12-2011

Open in new window


What I need to do is could the distinct values in A for each grouping of B

So the out put needs to look like

10-2011 3
11-2011 3
12-2011 4

Open in new window


Any ideas how I can accomplish this in Excel 2003?

Thanks
PCCUtechAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Assuming you have the first month (10-2011) in D2 you can use this "array formula" in E2

=SUM(IF(FREQUENCY(IF(B$3:B$20=D2,IF(A$3:A$20<>"",MATCH(A$3:A$20,A$3:A$20,0))),ROW(A$3:A$20)-ROW(A$3)+1),1))

confirm with CTRL+SHIFT+ENTER and copy down

That works for any type of data in column A, if it will always be numeric data in that column then you can simplify to

=SUM(IF(FREQUENCY(IF(B$3:B$20=D2,A$3:A$20),A$3:A$20),1))

also confirmed with CTRL+SHIFT+ENTER

regards, barry

Edit: example now attached
count-distinct.xls
0
 
yawkey13Commented:
I think you are looking for the "Subtotal" functionality.  I have attached a screenshot on the configuration window.  Just highlight the two columns and click on the "Subtotal" icon in your toolbar.  I am running a later version of Excel, so your screen might look slightly different.
3-21-2012-3-30-37-PM.png
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
PCCUtechAuthor Commented:
@netjgrnaut:  Been there and that won't help because, as an example, 1111 needs to be considered unique 3 times (1 for each different bucket in column B)

@yawkey13: I don't see a subtotal icon in my tool bars.  I am using excel 2003 and just have the "subtotal" icon.  Nor do I find that in any of the other tool bars.  Perhaps you are using a different version of excel or have a specific add-in enabled?
0
 
PCCUtechAuthor Commented:
Exactly what I was looking for... had to change it a bit for my exact sheet but this is it.

I completely suck at array formulas LOL>

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.