Solved

# Help counting distinct Records

Posted on 2012-03-21
Medium Priority
424 Views
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
0
Question by:PCCUtech
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 6

Expert Comment

ID: 37749402
0

LVL 6

Expert Comment

ID: 37749421
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

Author Comment

ID: 37749539
@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

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 37749550
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

LVL 6

Expert Comment

ID: 37749726
0

Author Closing Comment

ID: 37749739
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month10 days, 3 hours left to enroll