# Thread: Count uniques per column value

1. ## Count uniques per column value

I have a large one year dataset (around 20,000 rows) where there are two main columns, one is the unique identifier (duplicated), the other is the employee assigned to that unique identifier (unique).

I am trying to develop a formula to count the number of unique employees assigned to each identifier, as an example below. Columns A-B is the data. Columns D-E is what I want the output to look like. So far I have tried some SUM and FREQUENCY, none that seem to work seamlessly or perhaps I am making the formula too complicated for what I need to do.

Thank you very much for your knowledge.

Excel 2013/2016
ABCDE
1ProjectEmployeeProjectEmployees
22017-00011411 - John2017-00011
32017-00021411 - John2017-00022
42017-0002212 - David2017-00031
52017-00031411 - John2017-00041
62017-00041411 - John2017-00051
72017-00051411 - John2017-00061
82017-00061411 - John2017-00071
92017-00071411 - John2017-00081
102017-00081411 - John2017-00093
112017-00091411 - John2017-00101
122017-0009212 - David2017-00111
132017-0009344 - Jason2017-00121
142017-00101411 - John2017-00131
152017-00111411 - John2017-00141
162017-00121411 - John2017-00152
172017-00131411 - John
182017-00141411 - John
192017-00151411 - John
202017-00153773 - Charles

Data Sheet

2. ## Re: Count uniques per column value

if i undertand correctly then, this should work in cell E2
=COUNTIF(A:A,D2)
fill down as needed

hth,
Ross

3. ## Re: Count uniques per column value

Try:
Assumes there are no blank columns in the Employee field.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down rows as needed.

 A B C D E 1 Project Employee Project Employees 2 2017-0001 1411 - John 2017-0001 1 3 2017-0002 1411 - John 2017-0002 2 4 2017-0002 212 - David 2017-0003 1 5 2017-0003 1411 - John 2017-0004 1 6 2017-0004 1411 - John 2017-0005 1 7 2017-0005 1411 - John 2017-0006 1 8 2017-0006 1411 - John 2017-0007 1 9 2017-0007 1411 - John 2017-0008 1 10 2017-0008 1411 - John 2017-0009 3 11 2017-0009 1411 - John 2017-0010 1 12 2017-0009 212 - David 2017-0011 1 13 2017-0009 344 - Jason 2017-0012 1 14 2017-0010 1411 - John 2017-0013 1 15 2017-0011 1411 - John 2017-0014 1 16 2017-0012 1411 - John 2017-0015 2 17 2017-0013 1411 - John 18 2017-0014 1411 - John 19 2017-0015 1411 - John 20 2017-0015 3773 - Charles

 Cell Formula E2 {=SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$20=\$D2,MATCH(\$B\$2:\$B\$20,\$B\$2:\$B\$20,0)),ROW(\$B\$2:\$B\$20)-ROW(\$B\$2)+1),1))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

4. ## Re: Count uniques per column value

In E2 control+shift+enter, not just enter, and copy down:

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

5. ## Re: Count uniques per column value

@Aladin - in your formula is there an advantage to using 1-(\$B\$2:\$B\$20="") vs \$B\$2:\$B\$20 < > ""

6. ## Re: Count uniques per column value

Thank you, @rpaulson @AhoyNC @Aladin Akyurek
All three formulas worked in the data sheet - Over hindsight, I did not realize it was possible to do @rpaulson 's method without reading the field with employee names, but just count for duplicated entries. It can be made to work since since all row possibilities can be deduped to uniques (e.g. no two rows were the same).

@AhoyNC and @Aladin Akyurek methods read through all the data, looked at duplicates and generated the count of employees based on the actual sum of matches per each identifier. This process took some time for the machine to parse the formula (not quick with ~20K rows!)

I have learned new formulas from this topic and appreciate the insight it has given me. Thank you, kind benefactors.

7. ## Re: Count uniques per column value

You're welcome the formulas that Aladin & I gave would take into account if an employee name was duplicated on a project. Aladin's also took into account if there was a blank cell.

8. ## Re: Count uniques per column value

Originally Posted by AhoyNC
@Aladin - in your formula is there an advantage to using 1-(\$B\$2:\$B\$20="") vs \$B\$2:\$B\$20 < > ""
No.

9. ## Re: Count uniques per column value

Originally Posted by anonemous
[...]
Over hindsight, I did not realize it was possible to do @rpaulson 's method without reading the field with employee names, but just count for duplicated entries. It can be made to work since since all row possibilities can be deduped to uniques (e.g. no two rows were the same).
[...]
If no employee is associated with a project more than once, the COUNTIF solution is good enough...

## User Tag List

#### Posting Permissions

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