# Count Distinct Records - Criteria

1. ## Count Distinct Records - Criteria

I need to count unique/distinct employee names in a list based on the area that they work. There is variable work areas and variable occurances of employees.
ie,
BH Jones
BH Jones
BH Smith
BH Smith
BH Smith
SP Gray
SP Gray
SP Brown

So I would get
BH = 2
SP = 2
Total = 4

2. ## Re: Count Distinct Records - Criteria

Are the names in separate column?

3. ## Re: Count Distinct Records - Criteria

Yes they are in seperate columns.
There are only 12 areas but up to a few thousand rows of data to count.
Areas are the factory section bh = boiler house etc.

4. ## Re: Count Distinct Records - Criteria

So if you list the first column items in say, D2 down..

so you have

BH in D2
SP in D3,

Then use formula in D2:

``Please Login or Register  to view this content.``
adjusting ranges to suit and then confirm with CTRL+SHIFT+ENTER not just ENTER and copy down

5. ## Re: Count Distinct Records - Criteria

You can use a formula like this in E2

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

where areas are in A2:A2000, names in B2:B2000 and specific area to count (e.g. BH) is in D2

formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

List your other areas in D3:D13 and then copy the formula down to get a unique count for each

6. ## Re: Count Distinct Records - Criteria

OMG!
Thanks so much, I have tried sooo many combinations of the frequency and just couldn't get it.
I used daddylonglegs method referencing my areas in a list.
You guys are great, thanks for the quick responses.

Cheers

Thommo

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1