# 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
``````

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
``````

Any ideas how I can accomplish this in Excel 2003?

Thanks
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
Author 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?
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

Experts Exchange Solution brought to you by