How do I count the number of unique names belonging to each organization in excel?

Question

I am trying to count how many people are in each organization. The data is structured like this:

Name    Organization  Task Completed  
name1    org1         task1  
name1    org1         task2   
name2    org1         task1  
name2    org1         task2  
name3    org2         task2

What I want is

Organization    # of people in organization  
org1            2  
org2            1

Any suggestions are greatly appreciated!

score 0 · Answer 1 · answered Jul 02 '18 at 20:06

0

You can add two columns on top of the name and organization columns, say 'people' and 'org'.

Then highlight the new table selecting the people and org range (do not include the headers) and create a pivot table in a new worksheet.

In the pivot table fields, leave organization column as the rows and move the persons or 'people' column to the summation values area. The resulting table will have unique organization names with the count of people as a final table.

The result is like so:

Row Labels  Count of name
org1        4
org2        1
Grand Total 5

answered Jul 02 '18 at 20:06

L0uis

703
5
8

When you say add two columns on top, do you mean rows? – Tactical spatula Jul 02 '18 at 20:10
I meant add headers to each column in the original data. So above name put people, and above Organization put org. This is just to find our way around in the table. – L0uis Jul 02 '18 at 20:13

score 0 · Accepted Answer · answered Jul 02 '18 at 20:13

0

Use an array formula in G2 and drag down

=SUM(--(FREQUENCY(IF($B$2:$B$6=$F2,MATCH($A$2:$A$6,$A$2:$A$6,0)),ROW($A$2:$A$6)-ROW($A$2)+1)>0))

You enter the formula with Ctrl+Shift+Enter. Then curly braces should appear as seen in image.

Data:

answered Jul 02 '18 at 20:13

QHarr

83,427
12
54
101

How do I count the number of unique names belonging to each organization in excel?

2 Answers2